Confused about sheets and "Select" vs "Activate"

[Robert Crandal]

I am able to run the following code below:

Workbooks("DataBook.xlsm").Sheets(4).Activate

However, why does the following code give an error message:

Workbooks("DataBook.xlsm").Sheets(4).Select ' ERROR!!!


I thought a sheet "activate" call was the same thing as a sheet
"select" call. Can anybody explain what is going on here??


thank u

 

[Gary Keramidas]

i'm guessing you're trying to select a sheet in a non-active workbook. do you
have more than 1 workbook open?

 

[Robert Crandal]

Yes, that is correct. I have several workbooks open.

I thought that Workbooks("DataBook.xlsm") would specify
the correct workbook and therefore that either
Sheets(n).Activate OR Sheets(n).Select would do the same
thing????

 

[Dave Peterson]

You have to make sure that the workbook is active first:

Workbooks("DataBook.xlsm").activate
Workbooks("DataBook.xlsm").Sheets(4).Select

I'd use this to save typing:

with Workbooks("DataBook.xlsm")
.activate
.Sheets(4).Select
end with

Same thing if you wanted to select a range on sheets(4).

Workbooks("DataBook.xlsm").activate
Workbooks("DataBook.xlsm").Sheets(4).Select
Workbooks("DataBook.xlsm").Sheets(4).range("x99").select

with Workbooks("DataBook.xlsm")
.activate
with .Sheets(4)
.Select
.range("x99").select
end with
end with

Another way if you're going to a range:

Application.goto Workbooks("DataBook.xlsm").Sheets(4).range("x99"), _
scroll:=true 'or false

 

[fisch4bill]

I tried to duplicate your situation by having two workbooks open and issuing
each of the statements you've listed. If the workbook that I tried to
"SELECT" the sheet in was not the active workbook, I got the error. As long
as I was selecting the sheet in the active workbook, however, there was no
problem. I could, however "ACTIVATE" any sheet regardless of which workbook
is active.

I think the the "SELECT" option only looks to the active workbook even when
you specify a workbook, whereas the "ACTIVATE" command seems to be able to
use anything within the Excel application. I get a similar response when
trying to "ACTIVATE" or "SELECT" a cell that's not in the active sheet.

Anyway, this is what I came up with, maybe some of the MVP folks here know
more about it, and may have to correct me if I'm mistaken.

HTH
Bill