How to get list of worksheet names?

[Robert Li]

I am working with a large Excel file that contains over
100 worksheets. I need a list of all the worksheet
names. Can someone tell me how to get this list, other
than copy each one at a time?

Thanks,
Robert

 

[J.E. McGimpsey]

I am working with a large Excel file that contains over
100 worksheets. I need a list of all the worksheet
names. Can someone tell me how to get this list, other
than copy each one at a time?

You can do it with a macro:

Public Sub ListSheets()
Dim i As Long
For i = 1 to Worksheets.Count
ActiveSheet.Cells(i, 1).Value = Worksheets(i).Name
Next i
End Sub

This macro will insert a new sheet at the left of your workbook and
list all the sheets in the workbook:

Public Sub ListSheetsToNewSheet()
Const SHTNAME As String = "Index to Sheets"
Dim indexSheet As Worksheet
Dim i As Long
On Error Resume Next
Worksheets(SHTNAME).Delete
On Error GoTo 0
With Worksheets.Add(before:=Worksheets(1))
.Name = SHTNAME
For i = 1 To Worksheets.Count
.Cells(i, 1).Value = Worksheets(i).Name
Next i
End With
End Sub

If you're unfamiliar with macros, take a look at David McRitchie's
Getting started with Macros page - it's Windows oriented, but most
of the information is directly applicable to Macs.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Michael R Middleton]

Robert Li -

I am working with a large Excel file that contains over 100 worksheets. I

need a list of all the worksheet names. Can someone tell me how to get this
list, other than copy each one at a time? <

My Mac isn't available now to check, but there's likely a Mac feature
similar to the following Windows method:

If you only need to make navigation easy (instead of using a macro to get a
list), you can right-click on the left-right arrows to the left of the
worksheet tabs to get a list.

- Mike Middleton, www.usfca.edu/~middleton

 

[Robert Li]

-----Original Message-----


You can do it with a macro:

Public Sub ListSheets()
Dim i As Long
For i = 1 to Worksheets.Count
ActiveSheet.Cells(i, 1).Value = Worksheets (i).Name
Next i
End Sub

This macro will insert a new sheet at the left of your workbook and
list all the sheets in the workbook:

Public Sub ListSheetsToNewSheet()
Const SHTNAME As String = "Index to Sheets"
Dim indexSheet As Worksheet
Dim i As Long
On Error Resume Next
Worksheets(SHTNAME).Delete
On Error GoTo 0
With Worksheets.Add(before:=Worksheets(1))
.Name = SHTNAME
For i = 1 To Worksheets.Count
.Cells(i, 1).Value = Worksheets(i).Name
Next i
End With
End Sub

If you're unfamiliar with macros, take a look at David McRitchie's
Getting started with Macros page - it's Windows oriented, but most
of the information is directly applicable to Macs.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
.