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
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