Skip to main content

Excel Macro: Create a Table of Contents of Sheets

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 of Sheets

'------------------ Modules ------------------
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
    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", _
'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.

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code 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. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>