list all worksheets in a workbook w/o manually typing

[Danielle HR]

I want to create a list of all the worksheets in one workbook, "table of
contents". The table of contents for a report - basically listing all
departments (that's what the tabs are)

I know there is an indexing function, but I don't know how to use it for
indexing or listing worksheets.

 

[Paul B]

Danielle, here is one way, you might also want to have a look here

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

Sub TOC()
'will add a sheet, name it Worksheet Names
'and list all sheets in the workbook
Dim ws As Worksheet, n As Integer
n = 2
With Worksheets.Add
.Name = "Worksheet Names"
.Move before:=Worksheets(1)
End With
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Worksheet Names" Then
Sheets("worksheet Names").Range("a" & n) = ws.Name
n = n + 1
End If
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003