Automation · VBA
Create a Table of Contents
Build a TOC sheet linking to every worksheet.
- Creates a TOC sheet
- Hyperlinks to every sheet
- Re-runnable (rebuilds the list)
' Builds a "TOC" sheet with a hyperlink to every other worksheet.
Sub CreateTableOfContents()
Dim ws As Worksheet, toc As Worksheet, i As Long
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets("TOC").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set toc = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
toc.Name = "TOC"
toc.Range("A1").Value = "Table of Contents"
toc.Range("A1").Font.Bold = True
i = 3
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "TOC" Then
toc.Hyperlinks.Add Anchor:=toc.Cells(i, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
i = i + 1
End If
Next ws
End SubFree to use and modify · ExcelBot — excelempowers.com
Adds a "TOC" sheet at the front with a clickable hyperlink to each worksheet — handy for big workbooks.
How to install:
- Open your workbook and press Alt + F11 to open the VBA editor.
- Choose Insert ▸ Module and paste the code (or File ▸ Import File for the .bas).
- Press F5 to run, or run it from Developer ▸ Macros.
- Save your file as .xlsm (macro-enabled) to keep the macro.
No VBA? In ExcelBot you can do the same thing by just asking the AI in plain language.
table of contents
index
navigation
índice
navegación
Rather not use VBA?
In ExcelBot you get the same result by asking the AI in plain language — no macros needed.
Try ExcelBot free Browse more macros
