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)

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.