SubScript out of range error

[Frank McLellan]

I do know of another way to accomplish this but I thought that this would
work
I declared a variable as
Public newsheet as Object
Before getting into the body of the macro I initialize a series of values.
To initialize values I use Select Case where I've entered the code
Case X
Set newsheet = Sheets("Page G.6")
Case Y
Set newsheet = Sheets("Page L.13")

When I run the macro I get a run time error - subscript out of range (-9)
This looks like an array error but I have declared the variable as an
Object. I've stepped through the macro and the macro fails the first time it
gets to "Set newsheet..." in the initialization phase.
What am I missing??

 

[JE McGimpsey]

I do know of another way to accomplish this but I thought that this would
work
I declared a variable as
Public newsheet as Object
Before getting into the body of the macro I initialize a series of values.
To initialize values I use Select Case where I've entered the code
Case X
Set newsheet = Sheets("Page G.6")
Case Y
Set newsheet = Sheets("Page L.13")

When I run the macro I get a run time error - subscript out of range (-9)
This looks like an array error but I have declared the variable as an
Object. I've stepped through the macro and the macro fails the first time it
gets to "Set newsheet..." in the initialization phase.
What am I missing??

Are your sheets really named "Page G.6" or "Page L.13"?

The Subscript Out of Range error indicates that XL can't find the
appropriate worksheet.

Another possibility is that another workbook is active - Sheets("Page
G.6") is equivalent to ActiveWorkbook.Sheets("Page G.6"). If another
workbook is active, XL won't find Sheets("Page G.6") in the active
workbook.

 

[Frank McLellan]

Are your sheets really named "Page G.6" or "Page L.13"?

The Subscript Out of Range error indicates that XL can't find the
appropriate worksheet.

Another possibility is that another workbook is active - Sheets("Page
G.6") is equivalent to ActiveWorkbook.Sheets("Page G.6"). If another
workbook is active, XL won't find Sheets("Page G.6") in the active
workbook.

Yes the sheets are call what I indicated and there is no other workbook
active at the point the variables are being set.
The "newsheet" is a sheet located in another workbook which is opened at a
later point in time. Basically the macro does a large volumn of calculations
then calls a Template which contains, for example, the worksheet "Page G.6"
and dumps the numbers there. As part of the procedure I have a statement
"newsheet.Select" which selects that sheet in the active workbook. Perhaps I
have the wrong impression as I though that a variable is set for future use
without it being used immediately.
To get around this I changes the declaration of the variable from Object to
String - e.g. Newsheet = "Page G.6" and then in the procedure instead of
newsheet.select I do Sheets(newsheet).Select - this works fine.