Have you ever had an Excel file with multiple worksheets? Wouldn’t it be nice to have a table of contents on a worksheet with links to each sheet? Many of us would shout, “Yes!”.
Unfortunately, Excel cannot do that by itself. But, by creating a macro, you can help Excel create the sheet. The website Computergaga even provides the code. There is one issue with the code, but it is resolved in the comments in their blog. I am providing the corrected code in this post.
Save As Macro-Enabled
You will want to save the Excel file as a Macro-Enabled Excel file. Just go to File and click on Save As. Change the Save As Type to Excel Macro-Enabled Workbook. See Figure 1.
Before you can start doing anything with macros, you need to go to the options and add the developer tab. Click on File, Options, and Customize Ribbon. See Figure 2.
Click in the checkbox next to Developer in the list on the right side. Then, click OK. The Developer ribbon has everything for creating a macro on it.
Add the Module Code
Open the workbook where you want to create the Table of Contents. On the Developer tab, click the Visual Basic button (on the very left). The VBA window will open. Select the Insert tab and choose Module. See Figure 3.
Copy and paste the following code into the module window.
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
Finish and Run the Macro
After pasting in the code, click the Save button. Then, click File and Return to Excel. On the Developer tab, click the Macros button and choose the CreateToC macro. Then click the Run button. See Figure 4.
After the macro runs, Excel will create a worksheet titled Table of Contents that will contain a link to each of the sheets in your workbook. If you add more sheets in the future, you just need to run the macro again. The Table of Contents worksheet will be removed and an updated one will replace it. See Figure 5.
Image Credit: https://www.flickr.com/photos/97741188@N04/