Getting & Using Sheet Names or Index in VBA code
 

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

Getting & Using Sheet Names or Index in VBA code
 

'The MsgBox in the following code displays
'each Sheet name in the workbook.

Sub SheetNames()

Dim strSheetName As String
Dim i As Long

For i = 1 To Sheets.Count
strSheetName = Sheets(i).Name
MsgBox strSheetName
Next i

End Sub


'Alternative method
Sub SheetNames_2()

Dim ws As Worksheet
Dim strSheetName As String

For Each ws In Worksheets
strSheetName = ws.Name
MsgBox strSheetName
Next ws

End Sub

--
Regards,

OssieMac


"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

Getting & Using Sheet Names or Index in VBA code
 

I tested your examples and they worked great! With a little additional code
I think this will give me the results I want. Thanks...

Regards,
rw


"OssieMac" wrote:

'The MsgBox in the following code displays
'each Sheet name in the workbook.

Sub SheetNames()

Dim strSheetName As String
Dim i As Long

For i = 1 To Sheets.Count
strSheetName = Sheets(i).Name
MsgBox strSheetName
Next i

End Sub


'Alternative method
Sub SheetNames_2()

Dim ws As Worksheet
Dim strSheetName As String

For Each ws In Worksheets
strSheetName = ws.Name
MsgBox strSheetName
Next ws

End Sub

--
Regards,

OssieMac


"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

Getting & Using Sheet Names or Index in VBA code
 

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

Getting & Using Sheet Names or Index in VBA code
 

Gord, thanks for this code as another way to do what I was looking for. This
works well too. I did have to declare 'i' because I use Option Explicit most
of the time but it worked fine. Thanks for your response.

Regards
rw


"Gord Dibben" wrote:

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