how do I create a quick directory of sheets in workbook

[Momturbo]

I have a workbook with 20-plus worksheets, each of them with the name of a
different employee. Is there a quick way to make a directory of all the
named sheets?

 

[pgarcia]

Sub listshtnames()
For i = 1 To Sheets.Count
Cells(i, "f") = Sheets(i).Name
Next i
End Sub

 

[Gord Dibben]

Private Sub ListSheets()
'list of sheet names starting at A1 on a new sheet named "List"
Dim rng As Range
Dim I As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "List" Then
rng.Offset(I, 0).Value = Sheet.Name
I = I + 1
End If
Next Sheet
End Sub

Note: this won't give you a clickable table of contents that allows sheet
browsing.

For that.........................

You can build a Sheet Navigation Toolbar as per Dave's code on Debra
Dalgleish's site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP