Auto Table of Contents: Never Hunt for a Tab Again

#The Real Cost of Tab Sprawl

Tax workpapers grow organically. What starts as a 5-tab workbook in January becomes a 25-tab monster by March. Tab names follow no consistent convention:

• “Sched A v2 FINAL”
• “Fixed Assets — DEPR (corrected)”
• “State-2025-Apport-CALC”

A table of contents sheet — auto-generated and hyperlinked — solves this for everyone who touches the workbook: preparer, reviewer, partner, and (years later) the poor soul doing the IRS audit response.

#Minimal Version

This version works on any workbook — no category rules, no naming conventions required. It skips hidden sheets and creates a clean, hyperlinked index in under a second.

Option Explicit

Sub QuickTOC()
    ' ── Quick Table of Contents ───────────────────────
    ' Creates a "TOC" sheet with hyperlinks to every
    ' VISIBLE worksheet. Skips hidden sheets. No
    ' category grouping — just a clean flat index.
    '
    ' Works on any workbook. No setup required.
    ' ──────────────────────────────────────────────────

    Dim tocSheet As Worksheet
    Dim ws As Worksheet
    Dim tocRow As Long
    Dim sheetCount As Long

    ' ── Remove old TOC if it exists ───────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("TOC").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' ── Add TOC as the first sheet ────────────────────
    Set tocSheet = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    tocSheet.Name = "TOC"

    ' ── Header ───────────────────────────────────────
    With tocSheet
        .Cells(1, 1).Value = "TABLE OF CONTENTS"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        .Cells(2, 1).Value = ThisWorkbook.Name & " — " & _
            Format(Now, "mmm d, yyyy")
        .Cells(2, 1).Font.Size = 9
        .Cells(2, 1).Font.Color = RGB(120, 120, 120)

        .Range("A4:B4").Value = Array("#", "Sheet")
        .Range("A4:B4").Font.Bold = True
        .Range("A4:B4").Interior.Color = RGB(50, 50, 50)
        .Range("A4:B4").Font.Color = vbWhite
    End With

    ' ── List visible sheets ───────────────────────────
    tocRow = 5
    sheetCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "TOC" Then GoTo NextSheet
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        sheetCount = sheetCount + 1

        ' Number
        tocSheet.Cells(tocRow, 1).Value = sheetCount
        tocSheet.Cells(tocRow, 1).HorizontalAlignment = xlCenter
        tocSheet.Cells(tocRow, 1).Font.Color = RGB(140, 140, 140)

        ' Hyperlinked sheet name
        tocSheet.Hyperlinks.Add _
            Anchor:=tocSheet.Cells(tocRow, 2), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:=ws.Name
        tocSheet.Cells(tocRow, 2).Font.Size = 10

        tocRow = tocRow + 1
NextSheet:
    Next ws

    ' ── Format and finish ─────────────────────────────
    tocSheet.Columns("A").ColumnWidth = 5
    tocSheet.Columns("B").ColumnWidth = 45
    tocSheet.Columns("B").AutoFit

    tocSheet.Range("A4").Select
    ActiveWindow.FreezePanes = True

    MsgBox sheetCount & " visible sheet(s) indexed.", _
           vbInformation, "Quick TOC"
End Sub

#How the minimal version works

Skip hidden sheets: If ws.Visible <> xlSheetVisible Then GoTo NextSheet — supporting schedules you don’t want printed or indexed stay out of the TOC.

No categories: Every sheet gets one row with a number and a hyperlink. That’s it. This is the version you drop into any workbook without thinking about naming conventions.

Hyperlinks: SubAddress:="'" & ws.Name & "'!A1" — clicking jumps directly to the sheet. The single quotes handle spaces and special characters in tab names.

#Full Version: With Categories

Once you’re comfortable with the minimal version, this extended version groups sheets by category, extracts descriptions from cell content, and applies professional formatting.

Option Explicit

Sub AutoTableOfContents()
    ' ── Auto Table of Contents ─────────────────────────
    ' Creates (or refreshes) a "TOC" sheet with hyperlinks
    ' to every worksheet in the workbook. Grouped by
    ' category prefixes when available.
    '
    ' Sheet-naming convention (optional):
    '   "Sched-A — Income"  → grouped under "Schedules"
    '   "Fixed Assets"       → grouped under category name
    '   Sheets without a recognizable prefix → "Other"
    ' ────────────────────────────────────────────────────

    Dim tocSheet As Worksheet
    Dim ws As Worksheet
    Dim tocRow As Long
    Dim sheetCount As Long
    Dim currentCategory As String
    Dim prevCategory As String
    Dim sheetName As String
    Dim i As Long

    ' ── Step 1: Create/refresh TOC sheet ───────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("TOC").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Add TOC as the first sheet
    Set tocSheet = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    tocSheet.Name = "TOC"

    ' ── Step 2: Style the TOC ──────────────────────────
    With tocSheet
        .Cells(1, 1).Value = "TABLE OF CONTENTS"
        .Cells(1, 1).Font.Size = 16
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 1).Font.Color = RGB(30, 30, 30)

        .Cells(2, 1).Value = ThisWorkbook.Name
        .Cells(2, 1).Font.Size = 10
        .Cells(2, 1).Font.Color = RGB(120, 120, 120)

        .Cells(3, 1).Value = "Generated: " & Format(Now, "mmm d, yyyy h:mm AM/PM")
        .Cells(3, 1).Font.Size = 9
        .Cells(3, 1).Font.Color = RGB(150, 150, 150)

        ' Column headers
        .Range("A5:C5").Value = Array("#", "Sheet Name", "Description")
        .Range("A5:C5").Font.Bold = True
        .Range("A5:C5").Interior.Color = RGB(50, 50, 50)
        .Range("A5:C5").Font.Color = vbWhite
        .Range("A5:C5").RowHeight = 22
    End With

    tocRow = 6
    sheetCount = 0
    prevCategory = ""

    ' ── Step 3: Build the sheet list ───────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "TOC" Then GoTo NextSheet

        sheetName = ws.Name
        sheetCount = sheetCount + 1

        ' ── Determine category ─────────────────────────
        currentCategory = GetCategory(sheetName)

        ' Print category header if it changed
        If currentCategory <> prevCategory Then
            tocSheet.Cells(tocRow, 1).Value = ""
            tocSheet.Cells(tocRow, 2).Value = currentCategory
            tocSheet.Cells(tocRow, 2).Font.Bold = True
            tocSheet.Cells(tocRow, 2).Font.Size = 11
            tocSheet.Cells(tocRow, 2).Font.Color = RGB(60, 60, 60)
            tocSheet.Range("A" & tocRow & ":C" & tocRow). _
                Interior.Color = RGB(240, 240, 240)
            tocSheet.Range("A" & tocRow & ":C" & tocRow). _
                Borders(xlEdgeBottom).LineStyle = xlContinuous
            tocRow = tocRow + 1
            prevCategory = currentCategory
        End If

        ' ── Sheet number ───────────────────────────────
        tocSheet.Cells(tocRow, 1).Value = sheetCount
        tocSheet.Cells(tocRow, 1).HorizontalAlignment = xlCenter
        tocSheet.Cells(tocRow, 1).Font.Color = RGB(140, 140, 140)

        ' ── Hyperlinked sheet name ─────────────────────
        tocSheet.Hyperlinks.Add _
            Anchor:=tocSheet.Cells(tocRow, 2), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:=ws.Name

        tocSheet.Cells(tocRow, 2).Font.Size = 10
        tocSheet.Cells(tocRow, 2).Font.Color = RGB(0, 100, 200)
        tocSheet.Cells(tocRow, 2).Font.Underline = xlUnderlineStyleSingle

        ' ── Description (extracted from sheet) ─────────
        tocSheet.Cells(tocRow, 3).Value = GetSheetDescription(ws)
        tocSheet.Cells(tocRow, 3).Font.Size = 9
        tocSheet.Cells(tocRow, 3).Font.Color = RGB(120, 120, 120)

        ' Alternate row shading
        If sheetCount Mod 2 = 0 Then
            tocSheet.Range("A" & tocRow & ":C" & tocRow). _
                Interior.Color = RGB(248, 248, 250)
        End If

        tocRow = tocRow + 1

NextSheet:
    Next ws

    ' ── Step 4: Add stats footer ───────────────────────
    tocRow = tocRow + 1
    tocSheet.Cells(tocRow, 1).Value = "Total sheets: " & sheetCount
    tocSheet.Cells(tocRow, 1).Font.Italic = True
    tocSheet.Cells(tocRow, 1).Font.Color = RGB(140, 140, 140)

    ' ── Step 5: Format columns ─────────────────────────
    tocSheet.Columns("A").ColumnWidth = 5
    tocSheet.Columns("B").ColumnWidth = 40
    tocSheet.Columns("C").ColumnWidth = 50
    tocSheet.Columns("B").AutoFit
    tocSheet.Columns("C").AutoFit

    ' Prevent accidentally editing the TOC
    tocSheet.Protect Password:="", UserInterfaceOnly:=True

    ' Freeze the header rows
    tocSheet.Activate
    tocSheet.Range("A6").Select
    ActiveWindow.FreezePanes = True

    MsgBox "Table of Contents created with " & sheetCount & _
           " sheet(s).", vbInformation, "Auto TOC"
End Sub

' ── Helper: categorize a sheet by its name ─────────────
Private Function GetCategory(ByVal sheetName As String) As String
    ' Strip any trailing parentheticals like "(2)" from copies
    Dim cleanName As String
    cleanName = sheetName

    ' Remove "Sheet1", "Sheet2" etc parentheticals
    If InStr(cleanName, " (") > 0 Then
        cleanName = Left(cleanName, InStr(cleanName, " (") - 1)
    End If

    ' Known category prefixes
    If cleanName Like "Sched*" Then GetCategory = "Tax Return Schedules": Exit Function
    If cleanName Like "State*" Then GetCategory = "State & Apportionment": Exit Function
    If cleanName Like "*Asset*" Or cleanName Like "*Depr*" Or _
       cleanName Like "*179*" Or cleanName Like "*Bonus*" Then
        GetCategory = "Fixed Assets & Depreciation": Exit Function
    End If
    If cleanName Like "*NOL*" Or cleanName Like "*Carryforward*" Or _
       cleanName Like "*Credit*" Then
        GetCategory = "Carryforward Schedules": Exit Function
    End If
    If cleanName Like "*Trial Balance*" Or cleanName Like "*TB*" Then
        GetCategory = "Trial Balance": Exit Function
    End If
    If cleanName Like "*M-*" Or cleanName Like "*Book-Tax*" Or _
       cleanName Like "*Rec*" Then
        GetCategory = "Reconciliations": Exit Function
    End If
    If cleanName Like "*JE*" Or cleanName Like "*Journal*" Or _
       cleanName Like "*Adjust*" Then
        GetCategory = "Journal Entries": Exit Function
    End If
    If cleanName Like "*Notes*" Or cleanName Like "*Checklist*" Or _
       cleanName Like "*TOC*" Or cleanName Like "*Reference*" Then
        GetCategory = "Reference & Notes": Exit Function
    End If

    GetCategory = "Other Workpapers"
End Function

' ── Helper: get a short description from a sheet ───────
Private Function GetSheetDescription(ByRef ws As Worksheet) As String
    ' Try cell B1 first (often used for subtitles)
    If ws.Cells(1, 2).Value <> "" And Not IsNumeric(ws.Cells(1, 2).Value) Then
        GetSheetDescription = Left(CStr(ws.Cells(1, 2).Value), 80)
        Exit Function
    End If

    ' Try cell A1
    If ws.Cells(1, 1).Value <> "" And Not IsNumeric(ws.Cells(1, 1).Value) Then
        GetSheetDescription = Left(CStr(ws.Cells(1, 1).Value), 80)
        Exit Function
    End If

    ' Count used rows as a fallback description
    Dim usedRows As Long
    usedRows = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    If usedRows > 1 Then
        GetSheetDescription = usedRows - 1 & " data rows"
    Else
        GetSheetDescription = ""
    End If
End Function

#Line-by-Line Walkthrough

#TOC as the first sheet

Set tocSheet = ThisWorkbook.Worksheets.Add( _
    Before:=ThisWorkbook.Worksheets(1))

The TOC sheet is inserted as the first tab so it’s always visible when the workbook opens. This is a deliberate UX choice — the index should be the first thing anyone sees.

#Category grouping with the GetCategory function

The helper function applies pattern matching to sheet names using Like with wildcards. This creates a grouped, hierarchical index that’s far easier to scan than a flat alphabetical list.

Example output:

Schedules
  1. Sched-A — Income
  2. Sched-E — Deductions

Fixed Assets & Depreciation
  3. Fixed Assets — Detail
  4. Depreciation — MACRS

Reconciliations
  5. M-1 — Book-Tax Differences
  6. M-3 — Schedule M-3 Detail

#Hyperlinks with SubAddress

tocSheet.Hyperlinks.Add _
    Anchor:=tocSheet.Cells(tocRow, 2), _
    Address:="", _
    SubAddress:="'" & ws.Name & "'!A1", _
    TextToDisplay:=ws.Name

The Address is empty (no external file), and SubAddress points to cell A1 on the target sheet. The single-quotes around the sheet name handle spaces and special characters in tab names.

#Sheet protection

tocSheet.Protect Password:="", UserInterfaceOnly:=True

UserInterfaceOnly:=True means macros can still modify the sheet but users can’t accidentally type over the hyperlinks. No password — this isn’t about security, just preventing fat-finger edits.

#Auto-description from sheet content

The GetSheetDescription function pulls text from common “title” cells (B1 or A1) to add context. This turns the TOC from a bare list of tab names into a mini-index you can actually scan.

#Adapt It