Is there a way to refer to a worksheet other than by index or name?
Something that won't change as long as the worksheet exists.

Worksheets(1) or Worksheets("some sheet name") will no longer be valid if
someone inserts another worksheet before this one, or changes this one's
name. I'd like to be able to refer to a particular worksheet even if its
name or position in the workbook changes.

Paul

Each worksheet has a codename that does not change.

You can see these codenames in the VBE.

Expand your workbook/project and you will see a list of sheets.

Sheet1(sheetname)

Sheet1 is the codename.

To get a list of these run this macro.

Sub CreateListOfSheetsOnFirstSheet()
Dim WS As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set WS = Worksheets(i)
.Cells(i, 1).Value = WS.Name
.Cells(i, 2).Value = WS.CodeName
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Mar 2007 11:38:59 -0700, "Paul Pedersen" wrote:

Is there a way to refer to a worksheet other than by index or name?
Something that won't change as long as the worksheet exists.

Worksheets(1) or Worksheets("some sheet name") will no longer be valid if
someone inserts another worksheet before this one, or changes this one's
name. I'd like to be able to refer to a particular worksheet even if its
name or position in the workbook changes.

That's perfect. Thanks!

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Paul

Each worksheet has a codename that does not change.

You can see these codenames in the VBE.

Expand your workbook/project and you will see a list of sheets.

Sheet1(sheetname)

Sheet1 is the codename.

To get a list of these run this macro.

Sub CreateListOfSheetsOnFirstSheet()
Dim WS As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set WS = Worksheets(i)
.Cells(i, 1).Value = WS.Name
.Cells(i, 2).Value = WS.CodeName
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Mar 2007 11:38:59 -0700, "Paul Pedersen"
wrote:

Is there a way to refer to a worksheet other than by index or name?
Something that won't change as long as the worksheet exists.

Worksheets(1) or Worksheets("some sheet name") will no longer be valid if
someone inserts another worksheet before this one, or changes this one's
name. I'd like to be able to refer to a particular worksheet even if its
name or position in the workbook changes.