Confused about sheets and "Select" vs "Activate"

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

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
 
i'm guessing you're trying to select a sheet in a non-active workbook. do you
have more than 1 workbook open?
 
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????
 
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
 
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
 
Back
Top