July 9, 2018

Create Table of Contents

How to create table of contents with a back link from every worksheet automatically with VBA. To improve our working in workbooks with large number of sheets we can create a new sheet call Table of Contents. From this table of contents worksheet we can quickly and easily navigate to the sheet of our choice. In fact, creating a back link from each of the multiple worksheets to our table of contents worksheet, can make our performance even more productive. Watch the video:

Watch this video on YouTube.

Here’s the complete VBA code to add a table of contents worksheet and create back links to it automatically:

Sub TableofContent()
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(“TableofContents”).Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = “TableofContents”
For i = 1 To Sheets.Count
If Sheets(i).Name = “TableofContents” Then
ActiveSheet.Range(“A1”).Value = “Table of Contents”
Else
With ActiveSheet
.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:=””, _
SubAddress:=Sheets(i).Name & “!A1”, _
ScreenTip:=Sheets(i).Name, _
TextToDisplay:=Sheets(i).Name
End With
End If

Next i
Range(“A:A”).Columns.AutoFit
ThisWorkbook.Save

insertBackLink

End Sub

Sub insertBackLink()
For i = 1 To Sheets.Count
Sheets(i).Activate
If Sheets(i).Name = “TableofContents” Then
Else
With ActiveSheet
.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(1, 1), _
Address:=””, _
SubAddress:=”TableofContents!A1″, _
TextToDisplay:=”Table of Contents”
End With
End If
Next i
End Sub

Further Reading:

Hyperlinks.Add Method (Excel)