If Excel workbook contains many sheets , a table of contents can be created to navigate easier between the sheets.
This process can be done with macro .
This macro will create a new sheet at the start of the workbook named "Workbook_Index" when workbook opened. If this sheet already exists it will remove it and will rebuild. The macro will then list the names of all the sheets in the workbook and insert a hyperlink for each one.Also,can be returned to table of contents when the "Esc" key is pressed while on any sheet.
The VBA code is displayed below. Copy and paste codes into the module of a workbook where you need to create a table of contents.
Fort this ;
-Press Alt+F11 keys on any sheet to open Visual Basic Editor (VBE).
-Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
-Copy the VBA code below and paste it to the right pane of the VBA editor ("Module1" window).
-Confirm the changes , close the workbook and reopen it.
Macro code to add into module :
Sub auto_open()
Call create_index
Call return_index
End Sub
Sub Index_page()
Sheets("Workbook_Index").Activate
End Sub
Sub create_index()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 1
NewSheet ("Workbook_Index")
For Each Page In Worksheets
Sheets("Workbook_Index").Cells(k, 2).Select
Sheets("Workbook_Index").Cells(k, 1).Value = m & "-"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
With Sheets("Workbook_Index")
.Columns(1).Interior.Color = RGB(215, 250, 198)
.Cells.RowHeight = 18
.Columns(1).Cells.HorizontalAlignment = xlHAlignRight
.Columns(2).Cells.HorizontalAlignment = xlHAlignLeft
.Columns(2).Interior.Color = RGB(255, 255, 163)
.Columns(1).EntireColumn.AutoFit
.Columns(2).EntireColumn.AutoFit
End With
End Sub
Function NewSheet(argCreateList)
For Each Worksheet In ThisWorkbook.Worksheets
If argCreateList = Worksheet.Name Then
Application.DisplayAlerts = False
Worksheet.Delete ' if found - delete it
End If
Next Worksheet
Worksheets.Add(Before:=Worksheets(1)).Name = argCreateList
End Function
Sub return_index()
Application.OnKey "{ESC}", "Index_page"
End Sub
