Excel - list worksheets in a workbook.

Asked By mepetey on 10-Nov-07 03:46 PM
I have a workbook that has upwards of 50 worksheets. Is there a simple way
of generating a list of those worksheets names?  I do not fancy having to do
it manually?


Gary Keramidas replied on 10-Nov-07 03:58 PM
you don't say where you want to list them, this will display them in the
immediate window
in the vb editor, do a control-G. if you want them in a specific place, post

Sub list_names()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
End Sub


mepetey replied on 10-Nov-07 04:20 PM
Thanks for the fast response. I would like to insert them as a list in a
separate worksheet, and use as a validation list.

Don Guillett replied on 10-Nov-07 04:22 PM
try this
for i=1 to worksheets.count
next i

Don Guillett
Microsoft MVP Excel
SalesAid Software
Gary Keramidas replied on 10-Nov-07 04:38 PM
then you can use something like one of these:

Sub list_names()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Range("A" & ws.Index) = ws.Name
End Sub


Sub list_names()
Dim i As Long

For i = 1 To Worksheets.Count
Worksheets("sheet1").Range("A" & i).Value = Worksheets(i).Name
End Sub


IanKR replied on 11-Nov-07 07:40 AM
This is something I use to generate a Table of Contents sheet in a workbook,
which includes a hyperlink to each sheet:

Sub TableOfContents()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets

If ws.Name = "Table of Contents" Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End If

Next ws

Set wsTOC =
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3

For Each ws In ActiveWorkbook.Worksheets

If ws.Name <> wsTOC.Name Then
wsTOC.Hyperlinks.Add _
Anchor:=wsTOC.Cells(r, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Name
wsTOC.Cells(r, 1).Value = ws.Name
r = r + 1
End If

Next ws

Cells.Font.Name = "Times New Roman"
Application.CommandBars("Web").Visible = True
Application.ScreenUpdating = True

End Sub
mepetey replied on 11-Nov-07 08:06 AM
Thanks to one and all for the help! much appreciated
Chet Newman replied to IanKR on 30-Dec-09 01:17 PM
The listing of worksheets is 99% of what I needed. What I would like to add are the contents from the B2, C21, and C32 cells from each worksheet.

Is this possible using your original source code?