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 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
Sheets(SheetName).Delete
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
Range("B4").Select
'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
Next
Range("B4:B" & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
wonderfully helpful. kudos to you.
This helped me for a long time. Suddenly the TOC began to justify CENTER instead of the desired LEFT justification.
What went wrong and how to fix?
Thanks.
I couldn’t say Mrs. H. Text by default in Excel is aligned to the left of a cell. Something must be positioning more centrally, but whether that be formatting, or hidden characters like spaces I couldn’t know.
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?
Cheers!
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.
Its hard to say without seeing the file. Aslong as your workbook is structured as per the video and the code is the same (with changes where necessary). There should not be a problem.
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?
Problem solved:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & Sheets(i).Name & “‘” & “!A1”, TextToDisplay:=i – 1 & “. ” & Sheets(i).Name
ActiveCell.Offset(2, 0).Select
Awesome work ExcelRookie
This works beautifully, and saved me a considerable amount of time! Thank you!
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
CS
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
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.
mine does not like the Sub CreateTOC()
I’m using 2013
The version should not be an issue. I cannot understand why it would not like Sub CreateTOC(). What error does it show?
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.
Hi Rasmus,
Hard to say without seeing what you have. The code provided should work fine, I would check it through.
Kind regards
Alan
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?
Do you already have a Table of Contents sheet? Trying creating a sheet with different name. There is a name conflict somewhere.
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.
I created a Table of Contents with the sheet names but under the sheet names, I want a list of titles on the linked sheet that I can click to go directly to that spot on the sheet. I know I can do this manually, but I want to set it up so that if I insert or delete rows, the links will update to the new location. I hope that makes sense.
Hi Julie,
Sure, this sounds like something that can be done. It is a little beyond this comment box though.
We would use either some dynamic array functions like FILTER to list the titles for each sheet dynamically. Or Power Query for that task.
Then use the HYPERLINK function along with a lookup such as INDEX-MATCH to locate and link to the added title.