out of range error
 

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

Many TIAs

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson

Dave, I want to have your grandchildren !!
XXXX

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson

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

(line from old Outer Limits TV show)

Matilda wrote:

Dave, I want to have your grandchildren !!
XXXX

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

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.

Matilda wrote:

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" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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.

Matilda wrote:

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" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

I think the suggested code should get you closer.

Matilda wrote:

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" wrote:

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.

Matilda wrote:

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" wrote:

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 wrote:

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" wrote:

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").Sel ect

Or maybe:

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

would be sufficient.

Matilda wrote:

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

Many TIAs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson