Macro to Create an Index Sheet With Links to Every Sheet

This VBA macro adds an Index sheet at the front of the workbook with a clickable link to every other sheet. A second version also puts a Back to Index link at the top of every sheet, so you can move around a large workbook in one click.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Video (Malayalam)

Version 1: Index sheet with links

Sub CreateIndex()
    Dim ws As Worksheet, i As Integer
    Sheets.Add(Before:=Sheets(1)).Name = "Index"
    For Each ws In Worksheets
        If ws.Name <> "Index" Then
            i = i + 1
            ActiveSheet.Hyperlinks.Add Cells(i, 1), "", _
                "'" & ws.Name & "'!A1", , ws.Name
        End If
    Next ws
End Sub

Run it and a new sheet called Index appears first, with every sheet name listed in column A. Click a name to jump to cell A1 of that sheet.

How it works

1. Sheets.Add(Before:=Sheets(1)) adds a sheet at the front and names it Index. The new sheet becomes the active sheet.
2. For Each ws In Worksheets goes through every worksheet, skipping the Index sheet itself.
3. i counts the sheets, so each link goes in the next row: A1, A2, A3…
4. Hyperlinks.Add makes a link in that cell. The address is empty because the link stays inside the workbook; the sub-address 'Sheet name'!A1 points to cell A1 of the sheet, and the sheet name is shown as the link text.
5. The single quotes around the sheet name make the link work for names with spaces, like Sales 2026.

Version 2: with a Back to Index link on every sheet

Sub CreateIndex()
    Dim ws As Worksheet, i As Integer
    Sheets.Add(Before:=Sheets(1)).Name = "Index"
    For Each ws In Worksheets
        If ws.Name <> "Index" Then
            i = i + 1
            ActiveSheet.Hyperlinks.Add Cells(i, 1), "", _
                "'" & ws.Name & "'!A1", , ws.Name
            ws.Rows(1).Insert
            ws.Hyperlinks.Add ws.Range("A1"), "", _
                "Index!A1", , "Back to Index"
        End If
    Next ws
End Sub

The two extra lines insert a new row at the top of each sheet and put a Back to Index link in cell A1. Now you can go from the Index to any sheet and straight back again.

Note: Version 2 inserts a row above your data on every sheet. If a sheet’s formulas refer to row 1, or the sheet has a fixed layout, use Version 1.

Things to know

• Run it once per workbook. If a sheet called Index already exists, the macro stops with an error. Delete the old Index sheet first. With Version 2, also delete the extra row at the top of each sheet, or you’ll get two Back to Index rows.
• Hidden sheets are listed too, but their links won’t open them until they’re unhidden.
• Chart sheets are not included; the macro lists worksheets only.
• For a list without links, see the list of worksheets macro.
• Prefer a formula? The HYPERLINK function can build the same links from a list of sheet names.

Related macros