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
