Can you make a variable worksheet name?

[ectoid]

This is driving me nuts...
Whether in VBA or Excel can you make a worksheet name a variable?

For example:
Set c=Sheets("=cell").Range(f6:f66)
where =cell is a variable cell reference.

 

[Tazguy37]

Do you want to change the worksheet name whenever you want in your code, or do you just want to read what it is currently? You can use ActiveSheet.Name. Something a little different might work better for what you're trying to do.

 

[ectoid]

Hey Taz,
Yeah, I'm trying to get the sheetnaming to be dynamic so that I can change where the name is coming from.
Does that make sense or no?

 

[Zack Barresse]

Create a Public Const variable. Place it in a standar module (at the top). Then just refer to that variable when needed. This makes it so you only have to change the name once.

Example ...

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> SHEETNAME <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "SheetName"

<SPAN style="color:#00007F">Sub</SPAN> foo()
    MsgBox SHEETNAME
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>