Great video. If you don't want to get involved in VBA you can right click on the arrows in the bottom left hand corner and that will give you a vertical list of all the sheets that you can click on.
Thank you very much, Jon! This will be extremely helpful for the Workbooks I create.Also congrats on your finish on the Excel Hash competition! Great work on your entry file.Regards, Kenny Mc
Thanks so much Ken! I really appreciate your support. It's looking like I didn't win the first Excel hash contest, but it was a lot of fun and we look forward to doing it again.
Hi Jon, Thank you so much for your video. It is very helpful! May I ask how to have hidden sheet not shown in the table of content? I've tried to change to "True" as below instruction in the Macro but it didn't work. 'Set variables Const bSkipHidden As Boolean = True 'Change this to True to NOT list hidden sheets Const sTitle As String = "B2". Thank you!
Thanks Alexandr. I know what you mean. It just takes time and practice. The more you step through the code (F8) and study it, the easier it becomes. I know you will get it! Thanks again! :-)
I am a novice with excel. I downloaded your automated Table Of Contents file for sheets, which works great. Is it possible to include data from each sheet on the same row that would auto populate with the TOC. I have 7 identical cells from each sheet I would like to display on TOC page.
Hi Jon; I am working with Excel for Mac and when I copy the sheet to another workbook, I receive an error stating: Run-time error '1004': Method 'Auto-Filter' of object 'Range' failed. Is this code incompatible on a Mac? Cheers
Hi Jon, Including hidden sheets in the table of contents, how can I edit the code to unhide the sheet and go to the sheet when clicking on the named sheet in the table. Thanks in advance and GREAT WORK!
Hi Jon, Thank you for your interesting videos! I saw in the end of this video how your table of contents gallery was auto updated. I would very much appreciate if you could share the file. I have tried for a few hours now to make it work, (i am really new at creating macros)
great video!! is it possible to let the VBA code clear only the first3 columns?i want to add other functions in this sheet but the macro removes it every time
Hello, Thank you for the work on putting together this Macros. I do have a few questions. (1) I keep getting a 1004 error about the auto filtering. And indeed the filtering feature isn’t showing. How can I fix this? (2) is there a way to add a notes column next to the sheet column. Anything I type there ends up disappearing when I come back to the TOC.
Is there a quicker way to generate a title next to each tab name on the index sheet? For example, sheet1 has a title in c1 named "insurance", sheet2 is "rent", What I did was using =sheet1! C1. It worked ok but I had to do it for each one. It is very time consuming if there are many sheets.
Hi Frederick, Great question! I updated the macro on the downloads page to allow you to skip hidden sheets. This is a toggle that you can turn on/off within the code by changing a variable. There is a paragraph below the code that explains more about the bSkipHidden variable that is used to set the preference for hidden sheets. I hope that helps. Thanks! :-)
Great video!! Do you happen to have a macro that can create a table of data from a Excel sheet column that contains comma separated values? The table could have values from 1-12 and each row of column data could have any combination of those 1-12 values. Ie., one row might have 2,3 while another row might have 6,9,10.
Great video, helped me a lot. Every time I click the TOC tab, the excel freezes for a while. I believe it is looking for all the tab updates.is there a way to make it better, may be disable the toc update unless requested.
Definitely better to have it manually updated. Just insert a form control button (developer tab) and assign this macro to that button. Don't forget to delete the first part of the macro so it no longer auto updates.
Hi Jon, this spreadsheet is one of my newest best friends! 🏆I do want to know if there is a line of code I can add to the macro that will highlight either the tab number or the hyperlink to the tab the same color that I have highlighted the tab? I've been a color coder all as far back as I can remember. = )
Hi Jayne, Awesome, I'm happy to hear it's your new best friend. 🙂 Here's code that will fill the cell that contains the sheet number with the tab color. .Offset(i).Interior.Color/? = ws.Tab.Color/? You can put that below this line in the macro. .Offset(i, 1).Value = i I hope that helps. Thanks again and have a nice day! 🙂 If you want to fill the sheet name color instead of the number, then offset one column to the right. .Offset(i).Interior.Color/? = ws.Tab.Color/?
This may be what I've prayed for as an easy way to organize worksheets but each time I try to move or copy the TOC tab elsewhere, it always fails and error message displays that says "we can't copy this sheet." Why?
Hi, exactly what I wantd:automatic Table of content. But,just one important note:I can't copy excel table in TOC sheet. Paste option is grayed out.I do Ctrl+C,get marching ants and when CTRL+V I get nothing. I can copy only as value with open clipoard, option with double click Ctrl+C. From web browser or some other source I get Paste/ Paste Special option. Strange.I was trying googling but got everything but solution. Any opinion? Greettings Ben
Hi Deirdre, This is due to the Cell Styles traveling with the worksheet. In this case the best bet is to just copy the VBA code to a new sheet within your existing workbook. I updated the code so that you will not need to modify it. You can just copy the two macros: Worksheet_Activate and TOC_List into the Sheet module of the new worksheet in your workbook. The updated macros are on the blog post page, and also in the file you can download on the following page. www.excelcampus.com/vba/table-of-contents-automatic-update/ I just updated the page, so it might take a few hours for the server cache to clear and display the updated code. However, the file you can download should be the most recent version. I hope that helps.
Fantastic thanks Jon; it was a workbook in work so I’ll try it again on Monday. Was really pleased even with the initial version though, made it so much easier to move around so I’ll be using it on all my workbooks. Deirdre
Deirdre Leigh You could add some code to the macro to update the "Hyperlink" style for your new workbook and add it to the TOC code in the worksheet module so that when you copy it, it does the update on activate too. Won't add any real overhead to the macro. Here is an example, just change the various elements to suit your needs: With ActiveWorkbook.Styles("Hyperlink").Font .Name = "Calibri Light" .Size = 14 .Bold = False .Italic = True .Underline = xlUnderlineStyleSingle .Strikethrough = False .ThemeColor = 11 .TintAndShade = 0 .ThemeFont = xlThemeFontMajor End With With ActiveWorkbook.Styles("Hyperlink").Interior .Pattern = xlSolid .PatternColorIndex = 0 .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With
I've found something strange using this macro (Excel 365.) On the "Table of Contents" worksheet that is created running the macro, on the 889th row, which is the 885th worksheet listed, I get the message "Reference not valid." This error persists each time I run the macro. Is there an Excel quirk that might crop up after a certain number of iterations in the loop - or other explanation for this?
Hi Suhail, Sorry about that. I forgot to put the link in the description. I added it there, and here is the link as well. www.excelcampus.com/vba/table-of-contents-automatic-update/
How would I do this, but instead of it making a contents of links in a lovely list, instead making a list of the same cell but across sheets ... So on the front page I'd have one column doing what this video is doing, but then in the next column it would be a corresponding list of referencing cell "c6" which is a short description.. so column A would list variation 1,2,3,4 and so on, and then column b would list a description of that sheet, which would be written in the same cell across the sheets..
Jon, Thank you. I have a question: I copied the tab to other file. It worked, except that the I have 4 tabs that contain charts, these tabs don't appear in the TOC. Is there something else I need to do? Thank you in advance.
Hi Carlos, Great question! I've updated the download page with a new file that works for Chart sheets. Here's an explanation of why it wasn't working. Chart sheets are different from Worksheets. These are tabs where the entire sheet is a chart, not charts on a sheet. Just clarifying for anyone else reading this. The loop used in the macro is for worksheet objects only. This is because we cannot create hyperlinks to Chart sheets. The hyperlink requires that a cell in the sheet be referenced, usually A1, and the Chart sheets do not have cells. So, the workaround is to use the Worksheet_FollowHyperlink event (macro) to select the Chart sheet with code when the hyperlink is clicked on the TOC. Since the hyperlink can't be linked to a chart sheet, the TOC macro just links the Chart sheet links to the TOC sheet. They go nowhere by themselves, and just point the sheet they are on. However, the FollowHyperlink event takes over after the link is clicked, and we have code to tell it to select the Chart sheet by using the sheet's name, that is the value in the cell. Isn't Excel amazing!?! :-) There is a workaround for just about everything. Here is the direct link to download that file. It might take awhile to clear the server cache and appear on the blog post page. www.excelcampus.com/filedownload/vba-macros/Table-of-Contents-Auto-Update-Macro.xlsm I hope that helps.
