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 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 Nexterror 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 = Falseto turns off Excel’s warnings so we don’t have to confirm this deletion. We use
Application.ScreenUpdating = Falseto 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.
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:
- 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. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA