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
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
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 wrote:
>
> 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
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").Select
>
> Or maybe:
>
> application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> >
> > 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").Select
> >
> > Or maybe:
> >
> > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > >
> > > 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").Select
> > >
> > > Or maybe:
> > >
> > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > >
> > > > 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").Select
> > > >
> > > > Or maybe:
> > > >
> > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > > >
> > > > > 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").Select
> > >
> > > Or maybe:
> > >
> > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > >
> > > > 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").Select
> > > >
> > > > Or maybe:
> > > >
> > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > > >
> > > > > 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").Select
> > > > >
> > > > > Or maybe:
> > > > >
> > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > > > >
> > > > > > 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").Select
> > > > > >
> > > > > > Or maybe:
> > > > > >
> > > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("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").Select
> > > > > > >
> > > > > > > Many TIAs
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks