Reference Ever Changing Sheet Name

[Majawat]

I have one sheet that constantly changes names. One minute it may be called "Steigl Goldbrau" and the next "Blue Moon".

I would like to be able to reference it with the Sheet() function but not sure how as the name inside would change...

Maybe keep a variable in the VBA with the name of the sheet and update it as necessary? Anyone know of a better way?

Thanks for any and all suggestions!

 

[texasalynn]

how does it get changed?

 

[Juan Pablo González]

You should use the CodeName for this instead. Or you could add a hidden name to the worksheet and look for it when trying to get a reference to the worksheet.

 

[jim may]

Each Sheet has 3 ways to identify itself.

1) User Name - What's displayed on the Sheet Tab (too easily Changable)
2) Sheet Index Number -- such as in the immediate window typing ? activesheet.index
3) CodeName - Permanent (Hidden/Official) name - in the VBE - the sheets properties window 1st position, named in left column (Name) << In ()'s -- Change the default name in the right-hand column to our choice, like MySheet.

Then refer to it in code simply as --- mysheet.activate

 

[Majawat]

how does it get changed?

I haven't figured out exactly how I'm going to do that actually... Thanks for pointing that out for me!

You should use the CodeName for this instead. Or you could add a hidden name to the worksheet and look for it when trying to get a reference to the worksheet.

How do I use the CodeName then? Like this, for example?

ThisWorkbook.VBProject.VBComponents("Sheet3").Activate

<HR>

What I'm trying to do here is have a cell on "BeerList" with a hyperlink. When that hyperlink is clicked, it opens an browser page, switches the ActiveSheet to the name changing sheet, then change the name of the now ActiveSheet to the value in the original cell.

 

[Majawat]

Ok! I got it. Thanks you Juan Pablo González for getting me started!

Instead of referencing the sheet with Sheets("Sheet1"), changing the name of a sheet via the CodeName and then activating the sheet goes like this:

Sheet1.Name = NAME
Sheet1.Activate

This way, no matter what the name is, using the Sheet1 will work. When using CodeNames you don't use the Sheets() function, just the code name.


For anyone curious, I got help via this website: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[texasalynn]

Sheet1.Name = NAME
Sheet1.Activate

I would suggest using a less common variable, like ws. Then once you have assigned it you can use that to call the sheet

Sheets(ws).Activate