Create an Excel Sheet Index with Hyperlinks using VBA

Large design workbooks become difficult to navigate when they contain many calculation sheets, check sheets, charts, and outputs. This macro creates a clean index sheet with hyperlinks to every visible worksheet.

Why it is useful

• Quickly navigate large engineering workbooks.
• Create a professional front sheet before sharing calculations.
• Works without any add-in or external dependency.

How to use it

1. Open your workbook.
2. Press ALT + F11.
3. Insert a new module.
4. Paste the code and run CreateWorkbookSheetIndex.

Complete VBA code

Option Explicit

' ToolsForEngineers.com
' Create a clean sheet index with hyperlinks to every visible worksheet.

Public Sub CreateWorkbookSheetIndex()
    Dim wb As Workbook
    Dim idx As Worksheet
    Dim ws As Worksheet
    Dim r As Long

    Set wb = ActiveWorkbook
    Set idx = GetOrCreateIndexSheet(wb, "_Index")

    Application.ScreenUpdating = False

    idx.Cells.Clear
    idx.Range("A1:D1").Value = Array("No.", "Sheet Name", "Used Range", "Go To Sheet")
    idx.Rows(1).Font.Bold = True
    idx.Rows(1).Interior.Color = RGB(222, 235, 247)

    r = 2
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> idx.Name Then
            idx.Cells(r, 1).Value = r - 1
            idx.Cells(r, 2).Value = ws.Name
            idx.Cells(r, 3).Value = ws.UsedRange.Address(False, False)
            idx.Hyperlinks.Add Anchor:=idx.Cells(r, 4), Address:="", _
                               SubAddress:="'" & ws.Name & "'!A1", _
                               TextToDisplay:="Open"
            r = r + 1
        End If
    Next ws

    idx.Columns("A:D").AutoFit
    idx.Activate
    idx.Range("A2").Select
    ActiveWindow.FreezePanes = True

    Application.ScreenUpdating = True
End Sub

Private Function GetOrCreateIndexSheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet
    On Error Resume Next
    Set GetOrCreateIndexSheet = wb.Worksheets(sheetName)
    On Error GoTo 0

    If GetOrCreateIndexSheet Is Nothing Then
        Set GetOrCreateIndexSheet = wb.Worksheets.Add(Before:=wb.Worksheets(1))
        GetOrCreateIndexSheet.Name = sheetName
    End If
End Function