Create a Sheet Index of Excel Worksheets

Index of all Microsoft Excel Worksheets

Got any Excel Questions? Free Excel Help

Creating a Index Worksheet

When working with Excel, many users end up with a lot of different Worksheets which then makes navigation cumbersome. This can be over come in a few ways, with the best being keeping the number of Worksheets in an Excel Workbook to a minimum. This not only helps with navigation, but also means a far more efficient spreadsheet design . Ideally one should store all raw data on one Worksheet, in a classic table format (headings across the top and data laid out beneath) and then use another Worksheet to extract, report, chart and filter out needed data. However, sometimes this is just not possible for many reasons which is why we will see how we can work-around by creating an index sheet.

Before we jump into that, let's first ensure you are aware of Excel's built in pop-up sheet index. To see this simply right click on the sheet scroll tabs to the left of the name tabs. By doing this you will see a pop-up menu like shown below;

Upon clicking "More sheets..." you will see all Worksheets listed in a nice scrollable ListBox as shown below

Worksheet Index

If this is still not what you need we can create a Worksheet Index. First add a new sheet to the Workbook and call it "Index" (optional). Next right click on the sheet name tab of the "Index" sheet and select "View Code". In here you should paste the code as shown below

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

Now click the top right X to get back to Excel proper and then Save. Now, each time you activate the Index sheet the old list will be cleared and a new one will be added. This ensures the index shown is always up-to date should you add or delete Worksheet. You will also note that a range based on the Worksheet name is added to each Worksheet in range A1. If this clashes with existing names, simply change;

.Range("A1").Name = "Start " & wSheet.Index AND Anchor:=.Range("A1")

.Range("B1").Name = "Start " & wSheet.Index AND Anchor:=.Range("B1")

Or any other cell. You will also note that on each Worksheet is a "Back To Index" hyperlink