Automatically Create a Table of Contents in Excel

1 Flares 1 Flares ×

If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.

Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.

This macro will create a new sheet at the start of the workbook named table of contents. If one already exists it will remove it. It will then list the names of all the sheets in the workbook and insert a hyperlink for each one.

Some of the techniques used within this macro include;

  • Using a counter loop on the sheets collection to loop through all the sheets of the workbook
  • Create and name a sheet during the macro
  • Insert hyperlinks for easier navigation of workbook content
  • Concatenation of strings for building the hyperlink address and working with a dynamic range
  • The ActiveCell object with Offset to move cells

Create a Table of Contents Macro Code

The VBA code is displayed below. Copy and paste it into the module of a workbook where you need to create a table of contents. This macro is designed to work in a file no matter how many sheets it contains, or what they are called.

Sub CreateTOC()

    Dim i As Byte
    Const SheetName = "Table of Contents"

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    If Sheets(1).Name = SheetName Then
    End If

    Sheets.Add Before:=Sheets(1)
    Sheets(1).Name = SheetName

    Range("B2").Value = SheetName

    With Range("B2").Font
        .Name = "Calibri"
        .Size = 14
        .Underline = xlUnderlineStyleSingle
        .Bold = True
    End With


   'Loop through each sheet and create a table of contents using each sheet name
    For i = 2 To Sheets.Count 

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=i - 1 & ". " & Sheets(i).Name
    ActiveCell.Offset(2, 0).Select


    Range("B4:B" & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub

20 thoughts on “Automatically Create a Table of Contents in Excel

  1. Alan,
    Thank you, saved me a TON of work!!
    There is so much junk out there.
    Why doesn’t MS just give us the ability to add this as an add-in?


  2. Hi Alan,

    The macro is working nicely in sheets where is no information. When I am trying to use macro in excel where is information in every sheets, your macro makes table of contents, but hyperlinks are not working. It says “Reference is no valid”. Could you advise? I am using excel 2010.

      • I think that the problem might be that my sheet names includes spaces, colors and some other special characters. Maybe I need to add some quotes, what do you think?

  3. Hi
    this is brillant thnak you so much, has saved so much time and effort.

    one thing that does not work and that is if my tab has a space bar i get reference is not valid. i have tried the above link excelrookie posted but still cant get it to work.

    this is what i have? am i doing something wrong?

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘” & “!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name
    ActiveCell.Offset(2, 0).Select

    thanks so much for you help


    • I’m not sure what you are doing wrong. I can only suggest it is the character you are using to enclose the sheet name in the Subaddress argument. It must be the single quote, or apostrophe.

      I tried it on mine, but instead of a direct copy and paste of your code I typed them in and it worked great.

      • Hi I am also trying this script and also get the ‘reference is invalid’ which from reading above is to do with having spaces in my sheet names. You mention that it could be to do with the quotes – can you please identify which area you are referring to? and how I can rectify this?

        I ucopy and pasted this part of the code into the code from the top of this post: ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘” & “!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name
        ActiveCell.Offset(2, 0).Select

        • Hi Terri, sorry for the delay I have been away. Yes it does sound like the space issue. The statement should work and can be copied and pasted into the relevant section of your script.

          ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name
          ActiveCell.Offset(2, 0).Select

  4. This worked perfectly once I added the quotes that EXCEL rookie wrote. I had several sheets with spaces and dashes and needed the quotes. You might need to play with the spacing as I encountered a syntax error the first time I copied over the code from EXCEL rookie.

  5. Hi

    I was hoping that your macro would help me out aswel.

    But I also get the “reference is not valid”. Im on excel 2010.

    I have tried altering the code a bit without any luck(I have no experience with macro) It seems to make the TOC without any issues, but when I click the hyperlinks, only the 2nd and 3rd works, the rest comes with a warning.
    I have no idea what to do.

  6. This worked very well but now i get a MSVB error Run-time error ‘1004’: cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.
    Greek to me. can you please help?

  7. My links are not working. It says references are not working.
    However, If i add a new tab and run the macros again it works for the new sheet but not the old tabs.

Leave a Reply

Your email address will not be published. Required fields are marked *