Worksheet name in Macro

[wammer]

How can I make it so that the worksheet names references in a macro wil
change as I change the worksheet name?

Thanks
Ca

 

[Guest]

Use the Set statement:
Set CurrSheet = ActiveSheet (or set CurrSheet = Sheets("HERMAN")
Then you can use
CurrSheet.Name = "WAMMER"
and still refer to Currsheet instead of Sheets("Herman") or Sheets("WAMMER")
eg: CurrSheet.Range("G5").Value = 22
will change the G5 to 22 no matter what its name is.

 

[wammer]

Thanks Bob -

It definitely works. Does this still mean that I need to go change th
Set CurrSheet within the macro every time I change the name? It ther
no way to make it change automatically?

Thanks
Cam

 

[Paul B]

wammer, you could also reference the worksheet name in VBA and not the
worksheet tab name, here if you change the tab name of sheet 2 the first
Macro will not work the second one will

Sheet tab name
Sheets("sheet2").Range("A3") = "25"

VBA sheet name
Sheet2.Range("A3") = "25"


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **