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
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"]
- What is Brilliant Directories - May 28, 2024
- What is LifterLMS in WordPress - May 28, 2024
- How to Draw a 50 x 30 Floor Plan in CorelDRAW - May 22, 2024
