How to Create Hyperlink Index of Sheets in Workbook

When dealing with numerous worksheets in one workbook, moving between sheets can be challenging. To simplify navigation, you can create a sheet index with hyperlinks. Manually adding hyperlinks for 20 or more sheets is time-consuming and reduces productivity. Hence, we'll provide you with a VBA code to generate a sheet index with hyperlinks quickly and effortlessly.

To apply VBA code for creating hyperlink sheet index, follow the steps below:

1. Go to the Developer tab.
2. Click on the Visual Basic command to open the Microsoft Visual Basic for Applications window.
3. Alternatively, hold down the Alt + F11 keys to do the same.
4. Then, Go to Insert tab and click on Module.
5. Paste the following VBA code in the Module window.

Sub CreateCustomIndex()
    
    ' Declare variables
    Dim showAlerts As Boolean
    Dim counter As Long
    Dim indexSheet As Worksheet
    Dim currentSheet As Variant
    
    ' Store the current state of DisplayAlerts and disable alerts
    showAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    
    ' Attempt to delete the existing "Sheet List Index" sheet, if it exists
    On Error Resume Next
    Sheets("Sheet List Index").Delete
    On Error GoTo 0
    
    ' Add a new sheet and name it "Sheet List Index"
    Set indexSheet = Sheets.Add(Sheets(1))
    indexSheet.Name = "Sheet List Index"
    
    ' Initialize the counter and set the header for the index sheet
    counter = 1
    Cells(1, 1).Value = "Sheet List Index"
    
    ' Loop through each sheet in the workbook
    For Each currentSheet In ThisWorkbook.Sheets
        ' Exclude the "Sheet List Index" sheet from the index
        If currentSheet.Name <> "Sheet List Index" Then
            ' Increment the counter and create a hyperlink in the index sheet
            counter = counter + 1
            indexSheet.Hyperlinks.Add Cells(counter, 1), "", "'" & currentSheet.Name & "'!A1", , currentSheet.Name
        End If
    Next
    
    ' Restore the original state of DisplayAlerts
    Application.DisplayAlerts = showAlerts
End Sub

• Press the F5 key or the Run button to execute the code. After running, you'll notice a new sheet called 'Sheet List Index' is added before all other worksheets. This sheet serves as an index with hyperlinks to each worksheet. If you want to use a different name for your index sheet, just replace 'Sheet List Index' with your preferred name in the VBA code.

Now, you can automatically create a sheet index with hyperlinks using VBA code. Let us know your customization preferences and provide feedback in the comments.