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
- 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.
This macro overwrites my first worksheet with the table of contents worksheet. I should have one additional worksheet after I run this. Can you please help?
I created a version which is a bit more robust to errors:
Sub CreateTableOfContents()
'Step 1: Declare Variables
Dim i As Long
Dim wb As Workbook
Dim TOCSheetName As String
Set wb = ActiveWorkbook
'Step 2: Delete Previous TOC worksheet if Exists
On Error GoTo 0
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'Set the name of the sheet
TOCSheetName = "ToC"
'Loop through all the sheets to see if ToC has been created before
Dim FoundTemp As Boolean
For Each currentSheet1 In wb.Worksheets
If currentSheet1.Name = TOCSheetName Then
Application.DisplayAlerts = False
wb.Sheets(TOCSheetName).Delete
Application.DisplayAlerts = True
End If
Next
On Error GoTo 0
'Step 3: Add a new TOC worksheet as the first worksheet
Sheets.Add.Name = TOCSheetName
Set TOCSheetObject = wb.Sheets(TOCSheetName)
TOCSheetObject.Move Before:=Sheets(1)
'Step 4: Start the i Counter
For i = 1 To Sheets.Count
'Step 5: Add Hyperlink
TOCSheetObject.Hyperlinks.Add _
Anchor:=TOCSheetObject.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
TextToDisplay:=Sheets(i).Name
'Step 5b 'Bring some data from the sheet to display in the ToC. Delete apostrophe to activate
'Cells(i, 2).Value = Sheets(i).Range("B1").Value
'Step 6: Loop back
Next i
'Reset display changes
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
How do I add another column to the TOC, which takes data from cell B6 say from every worksheet?