Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

How To Create a Table of Content In Excel By Using VBA

To Create a table of content in excel with macro we have to use a Hyperlink.Add Function In VBA which is similar to the Excel’s Hyperlink Function.

Use Hyperlink.Add Method To Create A Table of Content

Use Hyperlinks.Add to insert a hyperlink in excel with VBA

It is the part of Hyperlink collection method. Let’s have a look at Arguments of  Add.Hyperlinks :

hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])

Create-a-Table-of-Content-In-Excel-By-Using-VBA-1

Anchor:  It can be a range or an object (Shape, Smart Art, Picture etc.). The anchor is like a storage place for the hyperlink.(Required)

Address & SubAddress  As highlighted from the name, both contain the address of the hyperlink. The Address contains the link (URL, Path to File etc.). to a particular file, web address or server path, whereas SubAddress contains the link to a particular place in that file or web page. (Required)

Screen Tip  It will highlight on the screen when mouse pointer paused on the hyperlink. And, if you don’t mention any ScreenTip in your code, then it will simply show the address of the hyperlink on screen. (Optional)

TextToDisplay This argument works to add a text in the anchor as a display. As it is an optional argument you can skip this. Without a TextToDisplay argument, the link address of the hyperlink will show at anchor. (Optional)

Let’s Create a Table of Content In Excel Using VBA Hyperlink.Add

In below macro have used For Each loop to get the name of all the sheets in a workbook & then it will insert a new worksheet &put all the hyperlinks into it.
Sub TableofContent()

Dim i As Long


On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Table of Content").Delete
Application.DisplayAlerts = True
On Error GoTo 0

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Content"

For i = 1 To Sheets.Count

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

Next i

End Sub

Download Sample File

Download Sample File To Learn About To Create a Table of Content In Excel

Conclusion

You can use above mentioned macro to create a table of content in any of your workbooks. You also add some formatting code in macro as per your need.