VBA code that you can use to create a table of contents in Excel.
VBA Code:
Sub CreateTableOfContents()
Dim ws As Worksheet
Dim tocSheet As Worksheet
Dim i As Long
' Create a new worksheet for the table of contents
Set tocSheet = ThisWorkbook.Worksheets.Add
tocSheet.Name = "Table of Contents"
' Set the initial row for the table of contents
i = 2
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Exclude the table of contents sheet itself
If ws.Name <> tocSheet.Name Then
' Add a hyperlink to the sheet in the table of contents
tocSheet.Hyperlinks.Add Anchor:=tocSheet.Cells(i, 1), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
i = i + 1
End If
Next ws
' Format the table of contents
With tocSheet
.Columns(1).AutoFit
.Range("A1").Value = "Table of Contents"
.Range("A1").Font.Bold = True
.Range("A1").Font.Size = 14
.Range("A1").HorizontalAlignment = xlCenter
End With
' Activate the table of contents sheet
tocSheet.Activate
' Inform the user that the table of contents has been created
MsgBox "Table of contents has been created in a new worksheet!"
End Sub
Join Our Telegram Group
Join Our WhatsApp Group
