I use an index page zIndex.
Sub ListSheets()
' List includes a hyperlink for each sheet
' - sorts the list
' - includes description from H1 of each Sheet
' - includes File Name in Column C
' - currently doesn't allow spaces in sheetnames
' This is OK since I do not put spaces in sheet names since
' such spaces create problems with a variety of formulas.
Dim i As Integer
Dim cRow As Long
Application.ScreenUpdating = False
cRow = 2
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(cRow - 1 + i, 3) = "'" & Application.ActiveWorkbook.Name
Cells(cRow - 1 + i, 4) = "'" & Sheets(i).Name
'Cells(cRow - 1 + i, 2).Value = "=indirect(address(1,8,,,rSheetNames))" ' see next line
Cells(cRow - 1 + i, 2).Value = "=INDIRECT(RC[2]&""!""&CELL(""address"",R1C8))"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(cRow - 1 + i, 4), Address:="", SubAddress:=Sheets(i).Name & "!A1"
Next
Cells(cRow - 1 + i, 4).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B1") = "Description"
Range("C1") = "File Name"
Range("D1") = "Sheet Names"
Rows("1:1").Font.Bold = True
With Range("A1").CurrentRegion
.Columns.AutoFit
.Font.Size = 12
End With
Application.ScreenUpdating = True
End Sub
This message was edited by Dave Patton on 2002-05-08 11:23