Worksheets("Sheet1").Name produces error

[IoMatua]

Hi.

I'm having an annoying little problem with using a sheet name to access a specific sheet from the Worksheets collection. Whenever I use the following line:

Worksheets("Sheet1").Name

I receive an error saying that the subscript is out of range, however the following works just fine:

Sheet1.Name

I need to get the first line to work so I can use a string variable in place the string literal. The function that this line will be in is passed an argument telling the function which sheet to access.

Any suggestions?

 

[xlbo]

Reason is that Sheet1 is the CODEname of the sheet. You probably don't have any sheets in the workbook called "Sheet1"
There may be a space like:
Sheet 1
whcih would cause this error - this particular error is caused because it can't find the object you are referring to

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Loomah]

OK
Sheet1 is the code name of the worksheet (I don't think it can be change)
Worksheets("Whatever&quot.name will return "Whatever". What you're doing in this case is asking for the name of a sheet for which you have specified the name. Pointless?

You will not have a shhet in your book called "Sheet1" which is why you get the error.

Either use the code name Sheet1 or you can use the index of the sheet ie Worksheets(1).name

Hope this helps
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

OH NO
here we go again with the copying thang!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Sorry Loomah - I'll wait a few minutes next time ;-)
Hey - at least it gives credence to my answers if you keep popping up and saying the same thing.....


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[TonyJollans]

Hi Loomah,

Just so you know, the Codename of the Sheet can be changed via the Properties Window.

Enjoy,
Tony

 

[Loomah]

Thanks Tony
You are, of course, right! I had a feeling that I'd seen that somewhere on this site before but couldn't confirm quickly so went with the wishy-washy "I don't think..." option!!

Ho-hum - need a drink
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[TonyJollans]

Bit early in the day for me, so I'll just have a coffee.

I'll have to use "I don't think .. " sometimes instead of "As far as I know .. ". It'd probably be true - I'm sure half the time I'm not thinking.

Cheers,
Tony

 

[IoMatua]

Thanks for clearing up the confusion. Most of the examples I had seen used names like Sheet1, Sheet2 etc as an argument to Worksheets(), so I just assumed it was that codename that was used, not the actual name of the sheet.