How to create index of sheets in Excel with hyperlinks

In this guide, we're going to show you how to create index page of worksheets in Excel with hyperlinks. Using VBA, you can automatically update the hyperlinks after adding or removing sheets.

Download Workbook

First, you need to create a new sheet for the index.

1. Create a new sheet.
2. Right-click on its tab.
3. Select View Code option to open VBA editor for the corresponding sheet.
   

Alternatively, you can press the Alt + F11 key combination to open the VBA window and select the index sheet from the left pane.

Copy the following code and paste into the editor. Once you pasted the code, it will run every time you open that worksheet.

Code for creating an index of sheets

Private Sub Worksheet_Activate()
    'Define variables
    Dim ws As Worksheet
    Dim row As Long
    row = 1
    'Clear the previous list and add "INDEX" title
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
    End With
    'Loop through each sheet to add a corresponding hyperlink by using the name of the worksheet
    For Each ws In Worksheets
        If ws.Name <> Me.Name And ws.Visible = xlSheetVisible Then
            row = row + 1
            Me.Hyperlinks.Add Anchor:=Me.Cells(row, 1), _
             Address:="", _
             SubAddress:="'" & ws.Name & "'!A1", _
             ScreenTip:="Click to go to sheet " & ws.Name, _
             TextToDisplay:=ws.Name
        End If
    Next ws
    'Adjust the width of first column by the longest worksheet name
    Me.Columns(1).AutoFit
End Sub

You can close the VBA window now and test by opening another sheet other than the index sheet and then go back to the index sheet. The code automatically updates the worksheets that are not hidden.

Remember to save your file as a macro-enabled workbook (xlsm).

Tweaks

The code creates hyperlinks for visible sheets only. We added this check to hide sheets used for calculations or static data which should not be accessible to the end users.

To remove this condition you can remove the And ws.Visible = xlSheetVisible part on the 13^th row.

Also, the last line in the subroutine adjusts the width of the first column based on the length of the longest worksheet name. Remove the entire 23^rd line, Me.Columns(1).AutoFit, to remove this when creating index of sheets.