Home > Excel Tutorial > Creating Table Of Contents Using Macro Codes

Creating Table Of Contents Using Macro Codes

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 creates a new sheet at the start of the workbook named “Workbook_Index” when workbook opened. If this sheet already exists it removes it and rebuilds. The macro then lists the names of all the sheets in the workbook and inserts 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

Example Workbook Can Be Downloaded Here

Advertisements
  1. Fernando
    January 14, 2017 at 8:02 pm

    Hello NetMerkez,
    good code. I appreciate it.
    Only a coment: If the cell for example had a name with a space (cell 1) the hyperlink doesn’t work,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: