Create Auto Table of Contents in Excel Workbook with VBA

When you create an auto table of contents in Excel for a workbook, you get a single index sheet that lists every worksheet with a clickable link, so nobody has to scroll through twenty sheet tabs again. Doing it by hand means adding a sheet, typing names and inserting hyperlinks one by one, and redoing it whenever sheets change. In this tutorial you will use a short VBA macro that builds the whole index in one click for any active workbook. You will also store it in your Personal Macro Workbook and put a Table of Contents button on the Excel ribbon.

⬇️ Download the Practice File
Seven daily sheets plus the Table of Contents macro · .zip containing an .xlsm (macro-enabled) · Excel 2010 and later

Table of Contents

Watch the Video Tutorial

PK runs the macro from a custom ribbon button, adds Back links to every sheet, walks through the code line by line with F8, and then moves it into the Personal Macro Workbook.

What You Will Build

A Table of Contents sheet added as the first sheet of the active workbook.
Serial numbers and hyperlinked sheet names for every visible worksheet, each with a screen tip.
Back links on each sheet that return you to the index.
A protection check so the macro stops politely on a protected workbook.
A ribbon button that runs the macro from your Personal Macro Workbook in any file.

The Example Workbook

The practice file has seven worksheets named Sunday to Saturday. Each one holds a small call-centre table in A2:F22 with the columns EMP ID, EMP Name, Login hours, Call Handled, AHT and Sales, and cell G1 holds a Back link. The file also contains the finished Table of Contents sheet and the macro in Module1, so you can delete the index sheet and rebuild it yourself.

Step 1: Add the Table of Contents Macro

Press Alt+F11 to open the VBA editor, choose Insert → Module and paste this code. It is the exact macro from the practice file.

Option Explicit

Sub Create_TableOfContents()

'Check is workbook is protected

If ActiveWorkbook.ProtectStructure = True Or ActiveWorkbook.ProtectWindows = True Then
    MsgBox "Workbook is protected. Please unprotect it first", vbCritical
    Exit Sub
End If

Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets.Add(before:=ActiveWorkbook.Sheets(1))

sh.Range("A1").Value = "Table of Contents"
sh.Range("A2").Value = "S.No."
sh.Range("B2").Value = "Worksheet"

Dim lr As Integer

Dim i As Integer

For i = 2 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Worksheets(i).Visible = xlSheetVisible Then
        lr = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
        sh.Range("A" & lr + 1).Value = lr - 1
        sh.Range("B" & lr + 1).Value = ActiveWorkbook.Sheets(i).Name
        sh.Hyperlinks.Add Anchor:=sh.Range("B" & lr + 1), Address:="", SubAddress:="'" & ActiveWorkbook.Sheets(i).Name & "'!A1", ScreenTip:="Click to go to " & ActiveWorkbook.Sheets(i).Name, TextToDisplay:=ActiveWorkbook.Sheets(i).Name
    End If
Next i

ActiveWindow.DisplayGridlines = False

sh.Range("A:A").ColumnWidth = 7
sh.Range("B:B").ColumnWidth = 30

With sh.Range("A1:B1")
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Font.Size = 12
    .Font.Bold = True
End With

With sh.Range("A2:B" & i)
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .Font.Size = 9
End With

sh.Range("A2:B2").Interior.ColorIndex = 15

On Error Resume Next
sh.Name = "Table of Contents"
On Error GoTo 0

End Sub

Save the file as a macro-enabled workbook (.xlsm), then run it with F5 or from Developer → Macros.

How the Auto Table of Contents in Excel Macro Works

1. The protection check

If the workbook structure or windows are protected, Excel will not let a macro add or rename sheets. So the code first tests ProtectStructure and ProtectWindows. If either is True, it shows the message Workbook is protected. Please unprotect it first and exits. In the video PK proves this by switching on Review → Protect Workbook without a password and stepping through the code.

2. Add the index sheet at the front

Sheets.Add(before:=ActiveWorkbook.Sheets(1)) inserts a new sheet before the current first sheet and stores it in the variable sh. The code then writes the title in A1 and the headers S.No. and Worksheet in A2 and B2.

3. Loop through the visible worksheets

The loop starts at i = 2 because sheet 1 is now the new index sheet, which should not list itself. The If ... Visible = xlSheetVisible test skips hidden sheets, so your helper or calculation sheets stay out of the index.

4. Find the next row and number it

lr is the last used row in column A, found with End(xlUp). The new entry goes on lr + 1, and the serial number is lr - 1. On the first pass the last row is 2 (the header), so the first serial number is 1 on row 3.

5. Add the hyperlink

Hyperlinks.Add does the real work:

Anchor is the cell in column B that holds the link.
Address:="" is empty because the link stays inside the workbook.
SubAddress is the target, for example 'Sunday'!A1. The single quotes keep sheet names with spaces working.
ScreenTip shows Click to go to Sunday when you hover.
TextToDisplay is the sheet name you see in the cell.

6. Format the index

After the loop, the macro hides gridlines, sets column A to width 7 and column B to width 30, merges A1:B1 as a bold 12-point title, sets the list to left-aligned 9-point text, and fills the header row with ColorIndex = 15, a light grey.

7. Rename the sheet safely

Finally it renames the new sheet to Table of Contents. The rename is wrapped in On Error Resume Next because a sheet with that name may already exist. In that case the new index keeps its default name (PK shows it arriving as Sheet21) instead of stopping with an error.

Step 2: Add Back Links to Every Sheet

An index is only half the navigation. You also want a quick way back. PK adds this by hand once and copies it to all sheets:

On the Sunday sheet, type Back in cell G1.
Press Ctrl+K (or right-click and choose Link) to open Insert Hyperlink.
Choose Place in This Document, select Table of Contents and keep the cell reference A1.
Click ScreenTip, type something like Click to go back, and click OK twice.
Copy G1, right-click a sheet tab, choose Select All Sheets (or Ctrl-click the tabs you need), and paste into G1. Because the sheets are grouped, the link lands on every one of them.

Right-click a tab and choose Ungroup Sheets when you are done, so later edits do not hit every sheet at once.

Step 3: Store the Macro in Your Personal Macro Workbook

To use the macro in any workbook, not only the practice file, move it to PERSONAL.XLSB, the hidden workbook Excel opens every time it starts.

If you have never used a personal macro, go to Developer → Record Macro, set Store macro in to Personal Macro Workbook, click OK and immediately click Stop Recording.
Press Alt+F11. You will now see VBAProject (PERSONAL.XLSB) in the Project window.
Delete the empty recorded macro, insert a new module in PERSONAL.XLSB and paste the Create_TableOfContents code.
Close Excel and choose Save when it asks about the personal macro workbook.

Because the code always works on ActiveWorkbook, it builds the index in whichever file you are looking at.

Step 4: Add a Table of Contents Button to the Ribbon

Right-click the ribbon and choose Customize the Ribbon, or go to File → Options → Customize Ribbon.
In the right-hand list select the tab you want, for example Insert, and click New Group. Click Rename and call it Worksheet.
In Choose commands from, pick Macros, select PERSONAL.XLSB!Create_TableOfContents and click Add.
Click Rename, type Table of Contents, pick an icon, and click OK twice.

Now open any workbook and click the new button. The index sheet appears at the front with a link to every visible sheet. If a Table of Contents sheet already exists, the new one keeps a default name, so delete the old index before you rebuild it.

Optional: An Improved Version of the Macro

Addition, not from the video: the original macro mixes Worksheets(i) and Sheets(i). That is fine in a workbook with only worksheets, but if the file also has chart sheets the two collections number differently and the wrong names can appear. This version loops through worksheets directly, rebuilds an existing Table of Contents sheet instead of adding a second one, and handles sheet names that contain an apostrophe.

Sub Create_TableOfContents_V2()
    Dim wb As Workbook, sh As Worksheet, ws As Worksheet, r As Long
    Set wb = ActiveWorkbook

    If wb.ProtectStructure Or wb.ProtectWindows Then
        MsgBox "Workbook is protected. Please unprotect it first", vbCritical
        Exit Sub
    End If

    On Error Resume Next
    Set sh = wb.Worksheets("Table of Contents")
    On Error GoTo 0

    If sh Is Nothing Then
        Set sh = wb.Worksheets.Add(Before:=wb.Sheets(1))
        sh.Name = "Table of Contents"
    Else
        sh.Hyperlinks.Delete
        sh.Cells.Clear
    End If

    sh.Range("A1").Value = "Table of Contents"
    sh.Range("A2").Value = "S.No."
    sh.Range("B2").Value = "Worksheet"
    r = 2

    For Each ws In wb.Worksheets
        If ws.Name <> sh.Name And ws.Visible = xlSheetVisible Then
            r = r + 1
            sh.Range("A" & r).Value = r - 2
            sh.Hyperlinks.Add Anchor:=sh.Range("B" & r), Address:="", _
                SubAddress:="'" & Replace(ws.Name, "'", "''") & "'!A1", _
                ScreenTip:="Click to go to " & ws.Name, TextToDisplay:=ws.Name
        End If
    Next ws

    sh.Activate
    ActiveWindow.DisplayGridlines = False
    sh.Range("A:A").ColumnWidth = 7
    sh.Range("B:B").ColumnWidth = 30
    With sh.Range("A1:B1")
        .Merge
        .HorizontalAlignment = xlCenter
        .Font.Size = 12
        .Font.Bold = True
    End With
    sh.Range("A2:B" & r).Font.Size = 9
    sh.Range("A2:B2").Interior.ColorIndex = 15
End Sub

Two details make it safer. Replace(ws.Name, "'", "''") doubles any apostrophe, which is how Excel expects it inside a quoted sheet reference. And the counter is a Long, so very large workbooks never overflow it.

Modern Excel Alternative: the Navigation Pane

Addition, not from the video: in Microsoft 365, View → Navigation opens a side pane that lists every sheet and the objects on it, and you can click to jump. It is not a visible index saved in the workbook, so for shared files the macro-built sheet is still clearer. In any version you can also right-click the small sheet scroll arrows at the bottom left to get a quick list of sheets.

Common Errors and Fixes

The message says the workbook is protected

Go to Review → Protect Workbook and switch it off (enter the password if one was set), then run the macro again.

The new index sheet is called Sheet21, not Table of Contents

A sheet named Table of Contents already exists, so the rename was skipped by On Error Resume Next. Delete the old index first, or use the improved version above, which rebuilds the existing sheet.

A link shows Reference isn’t valid

The sheet was renamed or deleted after the index was built, or its name contains an apostrophe. Rebuild the index; the improved macro handles apostrophes.

The macro button does nothing in a new workbook

The code is in the practice file instead of PERSONAL.XLSB, or macros are disabled. Move the code to the Personal Macro Workbook and check File → Options → Trust Center settings.

Tips

Hide helper and lookup sheets before running the macro so the index only shows sheets readers need.
Rebuild the index after adding, renaming or reordering sheets; it takes one click.
Put the Back link in the same cell on every sheet (G1 in the practice file) so users always know where to find it.
Microsoft documents every argument of the Hyperlinks.Add method on Microsoft Learn.

Want It Ready-Made?

If you would rather not maintain the code yourself, PK’s free PK’s Utility Tool V1.0 has a Worksheet Manager with a Create Table of Contents button, as shown at the end of the video. For bigger automation jobs, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles eight ready-to-use VBA tools, and you can browse more Excel VBA tools there.

Frequently Asked Questions

Can Excel create a table of contents automatically?

Excel has no built-in table of contents command, but the short VBA macro in this tutorial adds an index sheet with a hyperlink to every visible worksheet in one click.

Does the macro include hidden sheets?

No. It only lists sheets whose visibility is set to visible, so hidden helper sheets stay out of the index.

How do I use the macro in every workbook?

Paste it into a module in your Personal Macro Workbook, PERSONAL.XLSB, and add a button for it through Customize the Ribbon. It then works on whichever workbook is active.

Why does the macro stop with a protected workbook message?

A protected workbook structure blocks adding and renaming sheets. Turn off Protect Workbook on the Review tab and run the macro again.

Will the table of contents update by itself when I add a sheet?

No. The index is built when you run the macro. Delete the old index and run it again, or use the improved version, which rebuilds the existing sheet.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

A workbook with many sheets is much easier to use with an index at the front. With this macro you can create an auto table of contents in Excel for any workbook in one click: it checks protection, adds the sheet, lists every visible worksheet with a hyperlink and formats the result. Add Back links to each sheet, keep the code in your Personal Macro Workbook and put a button on the ribbon, and every large file you build will be easy to navigate. Download the practice file and try it on your own workbooks.

Create Auto Table of Contents in Excel Workbook (VBA Macro)

Watch the Video Tutorial

What You Will Build

The Example Workbook

Step 1: Add the Table of Contents Macro