Creating Table Of Contents

       
         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