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.
First, you need to create a new sheet for the index.
- Create a new sheet.
- Right-click on its tab.
- 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).
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 13th 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 23rd line, Me.Columns(1).AutoFit, to remove this when creating index of sheets.