Create A List Of Excel Worksheets

Over the past few weeks I’ve been working in an Excel file that contains many worksheets. Switching back and forth between worksheets using the scroll buttons has been nothing less than annoying. Then I remembered the hand dandy feature of Excel that lets you see a list of all worksheets. All you need to do is right one of the scroll buttons and a list of worksheets appear. Selecting one of the worksheets takes you right to that tab.

Now not everyone who opens the spreadsheet knows about this feature. When you share an Excel file with others, it would be nice if it could include a table of contents or index of worksheets. It is possible to do in Excel but requires a few steps.

  1. Open your Excel file.
  2. Insert a new worksheet at the beginning of the workbook. To do this, right click the first worksheet and click Insert. Select worksheet and click OK.
  3. Right click the new worksheet and click Rename. Type Index as the name and press Enter.
  4. Click the Tools menu, point to Macros, and click Visual Basic Editor.
  5. Click the Index worksheet and from the View menu click Code.
  6. Paste the following code in:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim l As Long

l = 1

With Me

.Columns(1).ClearContents

.Cells(1, 1) = “INDEX”

.Cells(1, 1).Name = “Index”

End With

For Each wSheet In Worksheets

If wSheet.Name <> Me.Name Then

l = l + 1

With wSheet

.Range(“A1″).Name = “Start_” & wSheet.Index

.Hyperlinks.Add Anchor:=.Range(“A1″), Address:=””, _

SubAddress:=”Index”, TextToDisplay:=”Back to Index”

End With

Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:=””, _

SubAddress:=”Start_” & wSheet.Index, TextToDisplay:=wSheet.Name

End If

Next wSheet

End Sub

Click the top right X to get back to Excel, and then save the Excel workbook.

When you reopen the workbook, the index should be listed with links to all the worksheets.

Article Written by

  • me

    Does not work in 2007, get to many syntax errors. I gave up after correcting 4 of them

  • Dave

    Did not work on Excel 2007.

  • Joao Monteiro

    Did not work on Excel 2007 neither Excel 2010 Beta.
    Always get syntax errors.
    Could you please test and mail the right code?
    Best regards
    Joao