Create Index in Excel – Create Table of Contents in Excel

To Create Index in Excel just copy the code, create a module in your Excel’s visual basic editor, then paste the code and run. This code not just creates a Table of content in Excel but also creates a Hyperlink to the respective sheets. It also creates a Hyperlink in all the sheets back to Index sheet. When you run the code for the first time this code creates a sheet named index and creates the table of content in it.

Please note: Please backup and run this code on the copy of your original file. And never run this code over and over again as it will create duplicates of hyperlinks in every sheet.

Disclaimer: The author of this code cannot be held responsible for any kind of data loss or damage. If you are running or using this code you should agree for this terms and conditions.

Sub CreateIndex()
Dim FirstCellAdd As String
Dim PrecentSheetAdd As String
Dim CombinedAdd As String
Dim FirstSheetAdd As String
FirstCellAdd = "!A1"
FirstSheetAdd = "Index"
FirstSheetCellAdd = FirstSheetAdd & FirstCellAdd
Worksheets.Add before:=Worksheets(1)
ActiveSheet.Name = "Index"
For a = 1 To Sheets.Count
    ActiveSheet.Cells(a, 1).Value = Sheets(a).Name
    PrecentSheetAdd = Sheets(a).Name
        
    CombinedAdd = PrecentSheetAdd & FirstCellAdd
    
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        CombinedAdd, TextToDisplay:=PrecentSheetAdd
    
    ActiveCell.Offset(1, 0).Select
Next a

For a = 2 To Sheets.Count
Sheets(a).Select

If ActiveSheet.Type <> 3 Then

ActiveSheet.Range("A1").Select
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert
ActiveSheet.Range("A1").Select


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        FirstSheetCellAdd, TextToDisplay:=FirstSheetAdd
Else
ActiveSheet.Next.Select
End If

Next a

End Sub