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.
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.
Dim i As Byte
Const SheetName = “Table of Contents”
.ScreenUpdating = False
.DisplayAlerts = False
If Sheets(1).Name = SheetName Then
Sheets(1).Name = SheetName
Range(“B2”).Value = SheetName
.Name = “Calibri”
.Size = 14
.Underline = xlUnderlineStyleSingle
.Bold = True
‘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
Range(“B4:B” & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone
.ScreenUpdating = True
.DisplayAlerts = True
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!