Access worksheet by name
 

sarasta Wrote:
So, what is your problem ? You have 2 ways to reference a sheet :
1. using indexing in a worksheets object collection : worksheets(a)
where a is an integer.
2. using naming worksheets("sheet_name")

There's nothing you can do more. :)

You don't understand the problem.
1. worksheets(a) can be used only the sheets are in order, so I can'
use it
2. a=2; worksheets("Sheet" & a) doesn't work

How can I use "a" as a parameter

--
stefante
-----------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...fo&userid=1359
View this thread: http://www.excelforum.com/showthread.php?threadid=26715

Access worksheet by name
 

All right, then first you should create a variable that would contain
the name of your sheet :

dim sheet_name as string

sheet_name="Sheet" & a

' you can view if it is correct :

msgbox sheet_name

' then you can reference a worksheet :

worksheets(sheet_name)


--
sarasta
------------------------------------------------------------------------
sarasta's Profile: http://www.excelforum.com/member.php...o&userid=10956
View this thread: http://www.excelforum.com/showthread...hreadid=267156

Access worksheet by name
 

The only reason
2. a=2; worksheets("Sheet" & a) doesn't work

wouldn't work is if the sheet name is not actually Sheet2. Otherwise it
should work. Sheet2 would need to be the name seen on the tab of the sheet.
If you are trying to use the code name of the sheet you would need to do
something like

Dim sh as Worksheet, sh1 as Worksheet
for each sh in Worksheets
if lcase(sh.Codename) = "sheet" & a then
set sh1 = sh
exit for
end if
Next
if sh1 is nothing then
msgbox "Sheet" & a & " was not found"
else
msgbox "Sheet" & a & " tab name is " & sh1.Name
End If
--
Regards,
Tom Ogilvy


"stefantem" wrote in message
...

sarasta Wrote:
So, what is your problem ? You have 2 ways to reference a sheet :
1. using indexing in a worksheets object collection : worksheets(a),
where a is an integer.
2. using naming worksheets("sheet_name")

There's nothing you can do more. :)

You don't understand the problem.
1. worksheets(a) can be used only the sheets are in order, so I can't
use it
2. a=2; worksheets("Sheet" & a) doesn't work

How can I use "a" as a parameter?


--
stefantem
------------------------------------------------------------------------
stefantem's Profile:
http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=267156