Rookie Question - Activating Worksheets

[John V]

This simple issue nags at me. In a larger macro, I start off activating a
worksheet and the current region, as follows:

ThisWorkbook.Activate
Worksheets("Transactions").Range("A1").Activate

[This inelegant code is about my fourth iteration at getting this right.]

I will run the macro from the VBA window, and if the Transactions worksheet
is not the active sheet when I start the macro, I will get the error message:
Activate method of Range class failed.

Seems to me that my code should work regardless of which workbook or
worksheet is active, but obviously I'm not understanding something. What is
it?

Thanks, John

 

[Sam Wilson]

You can write virtually any macro without using activate (it's faster when it
runs that way)...

But as a quick win, change:

Worksheets("Transactions").Range("A1").Activate

to

worksheets("Transactions").activate
range("A1").select

 

[JP]

If you check the online help for Activate (found at
http://msdn.microsoft.com/en-us/library/aa221681(office.11).aspx),
you'd see that it only changes the focus to a cell within a
selection.

You probably want the Select method instead, although as Sam
suggested, you can do most tasks in VBA without selecting anything.

--JP