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.
Table of Contents
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
- Step 1 declares an integer variable. The i variable serve as the counter as the macro iterates through the sheets.
- 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.
- 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.
- 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.
- 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.
- 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:
- Activate the Visual Basic Editor by pressing ALT+F11.
- Right-click the project/workbook name in the Project window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module.