out of range error

[Matilda]

Help please! Why am I getting a Runtime error 9 subscript out of range error
on this statement?
Workbooks("filename.xls").Worksheets("Sheet1").Select

Many TIAs

 

[Dave Peterson]

Is there a workbook named Filename.xls open?
If yes, does that workbook have a worksheet named Sheet1?

And if yes to both, is that workbook the active workbook--you can only select a
worksheet on the active workbook.

Workbooks("filename.xls").Select
Workbooks("filename.xls").Worksheets("Sheet1").Select

Or maybe:

application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
scroll:=true

would be sufficient.

 

[Matilda]

Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
tried to activate it and select it thus:
Workbooks("filename.xls").Activate
Sheets("Sheet1").Select
and got the exact same error.
I was trying to refer to the system label for the worksheet rather than the
user defined string.
I want to take a string from workbook 1 and search for its match in workbook 2

 

[Dave Peterson]

system label = Codename
(the name you see in the VBE project explorer?)

dim wks as worksheet
with workbooks("Filename.xls")
.activate
for each wks in .worksheets
if lcase(wks.codename) = "sheet1" then
wks.select
exit for
end if
next wks
end with

 

[Matilda]

Dave, I want to have your grandchildren !!
XXXX

system label = Codename
(the name you see in the VBE project explorer?)

dim wks as worksheet
with workbooks("Filename.xls")
.activate
for each wks in .worksheets
if lcase(wks.codename) = "sheet1" then
wks.select
exit for
end if
next wks
end with

 

[Dave Peterson]

It's a cookbook!!!!!!!!!!!

(line from old Outer Limits TV show)

Dave, I want to have your grandchildren !!
XXXX

 

[Matilda]

Sorry Dave, I just can't get it to work. Had a few days break to see if a
fresh start helps but no deal. I have a heap of code that does work, I just
can't get it to open the appropriate worksheet to work on!
It should be simple ... can you comment your code for me so I can see why it
isn't doing what I want it to do?

two workbooks
workbook1 has valueIwantTo Find In Workbook2
workbook2 has n sheets, match will be in one of them
need to open wkbk2 and run search routine (which works fine)

Sorry to be a pain

 

[Dave Peterson]

Ok.

'declare a worksheet variable
dim wks as worksheet

'start with the other workbook
with workbooks("Filename.xls")
'if you want to select, that workbook has to active
.activate
'loop through all the worksheets in that filename.xls workbook
for each wks in .worksheets
'if the codename--the name you see in the project explorer
'Sheet1(nameuserseesontab)
if lcase(wks.codename) = "sheet1" then
'is equal to sheet1, we found it, so select it
wks.select
'and get out
exit for
end if
next wks
end with

But maybe I misunderstood what "system label" meant.

Sorry Dave, I just can't get it to work. Had a few days break to see if a
fresh start helps but no deal. I have a heap of code that does work, I just
can't get it to open the appropriate worksheet to work on!
It should be simple ... can you comment your code for me so I can see why it
isn't doing what I want it to do?

two workbooks
workbook1 has valueIwantTo Find In Workbook2
workbook2 has n sheets, match will be in one of them
need to open wkbk2 and run search routine (which works fine)

Sorry to be a pain

 

[Matilda]

Ah... I see where I misled you. The system label I meant was Sheet1 (visible
in VBA Explorer) as opposed to the label the user sees on the tab. Sheet 1 is
a variable and not a property, then.

Thanks Dave, I'll have another go at it. You have the patience of a saint

 

[Dave Peterson]

That "system label" is called the codename of the worksheet.

I think the suggested code should get you closer.

Ah... I see where I misled you. The system label I meant was Sheet1 (visible
in VBA Explorer) as opposed to the label the user sees on the tab. Sheet 1 is
a variable and not a property, then.

Thanks Dave, I'll have another go at it. You have the patience of a saint