Table of Contents with Macro in Excel
Link to full course: https://commercecurve.com/ultimate-ex...
Take this course if you're looking to accelerate your career with a complete course that integrates finance and accounting topics with end-to-end Excel training through the application of concrete examples of real world companies.
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 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.
Steps:
1. Open any excel sheet and click Alt+F11, or go to "Developer" then "Visual Basic".
2. Save as your excel file as Macro enable excel file.
3. Go to "Insert" then "Module".
4. Copy the text from the below, and Paste it on Macro Visual Basic notepad.
Sub CreateTOC()
Dim i As Byte
Const SheetName = "Table of Contents"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
If Sheets(1).Name = SheetName Then
Sheets(SheetName).Delete
End If
Sheets.Add Before:=Sheets(1)
Sheets(1).Name = SheetName
Range("B2").Value = SheetName
With Range("B2").Font
.Name = "Calibri"
.Size = 14
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
Range("B4").Select
'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
ActiveCell.Offset(2, 0).Select
Next
Range("B4:B" & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
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.