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

Thanks
Ca

--
Message posted from http://www.ExcelForum.com

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 " wrote:

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

Thanks
Cam


---
Message posted from http://www.ExcelForum.com/

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


Bob Umlas, Excel MVP wrote:
*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") o
Sheets("WAMMER")
eg: CurrSheet.Range("G5").Value = 22
will change the G5 to 22 no matter what its name is.

"wammer " wrote:

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

Thanks
Cam


---
Message posted from http://www.ExcelForum.com/



--
Message posted from http://www.ExcelForum.com

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 **
"wammer " wrote in message
...
Thanks Bob -

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

Thanks
Cam


Bob Umlas, Excel MVP wrote:
*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 " wrote:

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

Thanks
Cam


---
Message posted from http://www.ExcelForum.com/

*


---
Message posted from http://www.ExcelForum.com/