Create a Table of Contents Using Macros In Excel

This is one of the most fun and coolest macros that you can use. If you have a lot of worksheets, it is very annoying to scroll left to right to find out what other worksheets you have. We can use Excel Macros to create a table of contents for easy navigation!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Creates a table of contents for all of the worksheets

Copy Source Code:

Sub CreateTableOfContents()

Dim counter As Long

On Error Resume Next
Application.DisplayAlerts = False
'If this worksheet already exists, let us redo this
Worksheets("Table of Contents").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'Let us add a new worksheet as our Table of Contents
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Contents"

'Let us enumerate all of the worksheets in our ToC
For counter = 1 To Sheets.Count
'This will add one hyperlink for the specific sheet
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(counter, 1), _
Address:="", _
SubAddress:="'" & Sheets(counter).Name & "'!A1", _
ScreenTip:=Sheets(counter).Name, _
TextToDisplay:=Sheets(counter).Name

Next counter

End Sub

Final Result: 

Exercise Workbook:

---

This is our list of worksheets:

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

 

STEP 3: Let us test it out!

Open the sheet. Go to Developer > Code > Macros

 

Make sure your macro is selected. Click Run.

 

With just one click, a new worksheet “Table of Contents” was created!

Try clicking the Americas link and it takes you straight to the Americas worksheet!

How to Create a Table of Contents Using Macros In Excel