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.

Worth $20, Absolutely Free

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.
  • Pingback: How To Add Developer Tab To The Excel Ribbon()

  • Inet Kemp

    thank you for your great examples!

    • Puneet Gogia

      Thanks Inet

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

  • Prashant Panchal

    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

      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
      .Columns(1).AutoFit
      End With
      End If
      Next i
      —————————–

      • Prashant

        Thank you so much

        • YasserKhalil

          You’re welcome. Glad I can offer some help