• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create Table Of Contents With Macro

kadrleyn

Member
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.

74874

Details,sample file at here : Excel table of contents
 

JJofSteel

New Member
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

New Member
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 <<<
Code:
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
 
Last edited by a moderator:
Top