How to Make Table Of Contents With Macro in Excel

If you have multiple tabs in a workbook and want to create an easy reference to each of them, a top-level table of contents tab in Excel is appropriate. A table of contents tab has a summary of all the sheets and each sheet name is clickable to bring the user to that specific tab.

Creating a table of contents can be a time-consuming activity if you have got a lot of different tabs, lessons, or materials as part of the list you’re preparing. There are a couple of benefits to automating this.

• It is time-consuming.
• If you have changes to your outline, it would be nice if that was automated too so that if there were certain changes to your outline, the table of contents would automatically update it.

Both of these things are achieved through this Excel Macro for creating and updating the table of contents.

Formula explanation:

1. Open any excel sheet and click Alt+F11, or go to Developer>Visual Basic.

2. Save as your excel file as Macro enables excel file.

3. Go to Insert>Module.

4. Copy the text from the below, and Paste it on Macro Visual Basic notepad.

Sub CreateTOC()

  Dim i As Byte
  Const SheetName = “Table of Contents”

With Application
  .ScreenUpdating = False
  .DisplayAlerts = False

End With
  If Sheets(1).Name = SheetName Then
  Sheets(SheetName).Delete

End If

  Sheets.Add Before:=Sheets(1)
  Sheets(1).Name = SheetName

Range(“B2”).Value = SheetName

With Range(“B2”).Font
  .Name = “Calibri”
  .Size = 14
  .Underline = xlUnderlineStyleSingle
  .Bold = True

End With
  Range(“B4”).Select

‘Loop through each sheet and create a table of contents using each sheet name

For i = 2 To Sheets.Count

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=Sheets(i).Name & “!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name

ActiveCell.Offset(2, 0).Select

Next

Range(“B4:B” & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone

With Application
  .ScreenUpdating = True
  .DisplayAlerts = True

End With

End Sub

5. Press F5 to Run the task.

6. The table of content is ready. Let’s check by clicking on any of the contents whether it works or not.

Following these steps will allow you to create a table of contents automatically with it linking to the supporting tabs using a macro within Excel. As you can imagine it, this can come in handy if you have a lot of different tabs and you want to create a table of contents for all of them within a top-level tab.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!