Create Index in Excel

Create Index in Excel

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.

Create Index in Excel
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

If ActiveSheet.Type <> 3 Then


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

Next a

End Sub

Other Excel Tutorials & Articles: 

Advanced Excel

[catlist name="advanced-excel"]

Excel Formulas

[catlist name="excel-formulas"]

Excel VBA Macro Lessons

[catlist name="excel-vba-macros"]

Excel Macro Examples

[catlist name="excel-macro-examples"]

Latest posts by himaghiri (see all)


Himaghiri Thanayan.N is the founder of HBN Infotech. He has great dreams and passion for Graphic Design, Web Design, CAD Design, Arts & Crafts, Science & Information Technology and Article Writing.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.