This macro will give you a list of sheetnames and their codenames.

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 Thu, 10 Apr 2008 14:14:02 -0700, rwjack
wrote:

How do you get each Sheet Name in a Workbook so you can use it in code? I
know that each Sheet, regardless of Name has an Index of 1 through n
beginning with the leftmost Sheet but I don't know how to use that
information.

I have one reference book, Excel 2003 VBA, that says there is a Worksheet
Property 'Name' that returns a String and in the Description of that Property
it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell
me how to use that in code.

I don't really care whether I use the name of each Sheet or the Index but if
you can use either it would be nice to know both ways.

I know a way to write the code if I know the Sheet names but I want the code
to be more flexible so I don't have to 'hard code' them.

Thanks
rw