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:
Excel VBA Macro Lessons
Excel Macro Examples
- Create Index in Excel
- Excel Macro Example – Insert Multiple Worksheets Based on Cell Data
- Combine Excel Sheets into one Sheet Macro
- Excel vba Userform tutorial – How to create number series using Userform
- Using Macro Recorder
- Excel VBA Input box tutorial – How to create number series using input box
- Excel macro examples – How to insert single or multiple worksheet based on active cell data or content
- How to create a number chart 1 to 100 using MS Excel macro