Create Table Of Contents With Macro

[kadrleyn]

In Excel , table of contents can be automatically created a with a macro.

This macro creates a new sheet named "Workbook_Index" as first sheet of workbook. The macro then lists in this sheet the names of all the sheets in the workbook and inserts a hyperlink for each one.User can be returned to index page when the "Esc" key is pressed while on any sheet.



Details,sample file at here : Excel table of contents

 

[JJofSteel]

This marco falls over if there are spaces in the sheet names.
Are you able to adapt it to allow spaces in the sheet names please?
Thanks JJ

 

[JJofSteel]

This marco falls over if there are spaces in the sheet names.
Are you able to adapt it to allow spaces in the sheet names please?
Thanks JJ

Don't worry I found a macro which can cope with the blanks in the sheet names. It is here:
https://www.extendoffice.com/documents/excel/5100-excel-create-table-of-contents.html
or like this:
>>> use code - tags <<<

Sub CreateTableofcontents()
'updateby Extendoffice 20180413
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Table of contents"
    I = 1
    Cells(1, 1).Value = "Table of contents"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Table of contents" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

 

[SouthKaDaaku]

In Excel , table of contents can be automatically created a with a macro.

This macro creates a new sheet named "Workbook_Index" as first sheet of workbook. The macro then lists in this sheet the names of all the sheets in the workbook and inserts a hyperlink for each one.User can be returned to index page when the "Esc" key is pressed while on any sheet.

View attachment 74874

Details,sample file at here : Excel table of contents

Hey Kadrelyn...

Thank you for this.

But I'm unable to go back when i press the ESC button. Any idea why it is happening so?

Cheers