Create a Table of Contents In Excel Using VBA

The following macro not only creates a table of contents at the beginning of the workbook, but it also adds hyperlinks so that you can easily click a hyperlink to jump to a sheet.

Create a Table of Contents for your workbook

Sub CreateTableOfContents()
'Step 1: Declare Variables
    Dim i As Long
'Step 2:  Delete Previous TOC worksheet if Exists
    On Error Resume Next
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    Sheets("TOC").Delete
    On Error GoTo 0
'Step 3:  Add a new TOC worksheet as the first worksheet
    ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
    ActiveSheet.Name = "TOC"
'Step 4: Start the i Counter
    For i = 1 To Sheets.Count
'Step 5: Add Hyperlink
    ActiveSheet.Hyperlinks.Add _
    Anchor:=ActiveSheet.Cells(i, 1), _
    Address:="", _
    SubAddress:="'" & Sheets(i).Name & "'!A1", _
    TextToDisplay:=Sheets(i).Name
'Step 6: Loop back
    Next i
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

How This Macro Works

1. Step 1 declares an integer variable. The i variable serve as the counter as the macro iterates through the sheets.
2. Step 2 attempts to delete any previous sheet called TOC. We use the On Error Resume Next error handler to start Step 2, because there may not be any TOC sheet to delete, this tells Excel to continue the macro if an error is encountered here. We then delete the TOC sheet using the Application.DisplayAlerts = False to turns off Excel’s warnings so we don’t have to confirm this deletion. We use Application.ScreenUpdating = False to turn screen updating off to speed up this macro code. Finally, we reset the error handler to trap all errors again by entering On Error GoTo 0.
3. In Step 3, we add a new sheet to the workbook using the Before argument to position the new sheet as the first sheet. We then name the sheet TOC.
4. Step 4 starts the i counter at 1 and ends it at the maximum count of all sheets in the workbook. When the maximum number is reached, the macro ends.
5. In Step 5, uses the Hyperlinks.Add method to add the sheet name and hyperlinks to the cell. This step feeds the Hyperlinks.Add method the parameters it needs to build out the hyperlinks.
6. Step 6 loops back to increment the i counter to the next count. Finally, You should turn screen updating on and turn display alerts on

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert -> Module.
   
4. Type or paste the code in the newly created module.