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])


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, _
End With

Next i

End Sub

Download Sample File

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


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.

Content Protection by


  1. Prashant Panchal 19 Jun, 17 at 2:12 pm - Reply

    How do I modify above code to generate sheet name list (hyper-linked of course) where sheet contains specific word, for example I have number of sheets starting with word “prepaid” and i want to create list for only sheets which starts with this word (prepaid) ?? please help

    • YasserKhalil 19 Aug, 17 at 6:39 pm - Reply

      Hello Prashant .. Try changing the loop to that
      Dim k As Long
      For i = 1 To Sheets.Count
      If Left(Sheets(i).Name, 7) = “Prepaid” Then
      With ActiveSheet
      k = k + 1
      .Hyperlinks.Add Anchor:=ActiveSheet.Cells(k, 1), Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘!A1”, ScreenTip:=Sheets(i).Name, TextToDisplay:=Sheets(i).Name
      End With
      End If
      Next i

      • Prashant 19 Aug, 17 at 6:47 pm - Reply

        Thank you so much

        • YasserKhalil 19 Aug, 17 at 6:48 pm - Reply

          You’re welcome. Glad I can offer some help

  2. Kevin Lehrbass 13 May, 17 at 9:08 pm - Reply

    Cool, thanks for the Table of Contents Excel file Puneet!

  3. Inet Kemp 21 Feb, 16 at 6:56 pm - Reply

    thank you for your great examples!

    • Puneet Gogia 3 Mar, 16 at 4:39 pm - Reply

      Thanks Inet

Leave A Comment