The Best Excel Table Of Contents That Automatically Updates

No video

The Best Excel Table Of Contents That Automatically Updates

Рет қаралды 48,936

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampu...
In this video I explain how to create a table of contents that automatically updates or refreshes when changes are made to the sheets in the workbook.
Download the file with the VBA macro code: www.excelcampu...
This is great if you add, delete, or change names of sheets, and want to automatically reflect those changes in your table of contents sheet. It keeps the table of contents always up to date and accurate.
This technique uses a VBA macro and the Worksheet_Activate event to perform the automatic refresh. The macro that creates the table of contents is called every time the TOC sheet is selected by the user, and it recreates the sheet list with hyperlinks to each sheet.
The table of contents macro I use includes page numbers (tab numbers) and enables the filters at the top of the list so the user can sort and search long lists. The table of contents macro is included in the download file.
You can also use your own TOC macro if you'd like. Just copy it into the sheet module and call it from the Worksheet_Activate event.
Related videos:
VBA Code Modules & How to Run Event Macros Based on User Actions: • How To Create Vba Code...
Table of Contents Gallery Macro: • How to Add a Table of ...
00:00 Introduction
00:13 Create Automatic Updates to Your Table of Contents in Excel
00:59 Adding a Macro to Create the Table of Contents
03:22 Use it in Your Own Workbooks

Пікірлер: 81

@henrymiszelowski5130 2 жыл бұрын

Great Video as usual Jon you are constantly teaching me how to be more efficient and effective using XL. Thank you so much Jon.

@Excel_Mantra 5 жыл бұрын

Hello, I am not too much expert in VBA. it is very nice video to understand it in step by step. *Great Jon*

@eucagwar 3 жыл бұрын

You explained it very easily!

@ahmedfahmynet 3 жыл бұрын

Well done

@Ramcupp Жыл бұрын

Awesome video! I have wasted hours watching other videos. This is the best by far. = )

@EASTERNLAVA Жыл бұрын

So cool!!!

@karinaadcock 5 жыл бұрын

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.

@ExcelCampus 5 жыл бұрын

Thanks for the tip Karina! :-)

@kambizelllm1475 Ай бұрын

Good Job!

@tajsay 4 жыл бұрын

That was a great video Jon..Thank you so much for this video..you have saved me tonnes of time ..thumbs up

@unitedworldcare9497 4 жыл бұрын

Excellent. That's what I needed. Thank you

@wayneedmondson1065 5 жыл бұрын

Hi Jon.. thanks for the video.. love the creativity behind your lessons and solutions. Thumbs up!

@sovsel 3 жыл бұрын

thank you

@sandeepkothari5000 5 жыл бұрын

Jon, You are great.

@ExcelCampus 5 жыл бұрын

Thanks Sandeep! :-)

@ekoprasetiyo 5 жыл бұрын

Thank you for the video. I shared this on my LinkedIn page. Hope this will help other's who need it.

@ExcelCampus 5 жыл бұрын

Thanks so much Eko! I appreciate your support! :-)

@kenmcmillan2637 5 жыл бұрын

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

@ExcelCampus 5 жыл бұрын

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.

@RobertOsorno 5 жыл бұрын

Excellent! Thank you Jon.

@michaelfullard4983 5 жыл бұрын

Great work as usual Jon, thanks.

@ExcelCampus 5 жыл бұрын

Thanks so much Michael!

@80andromeda08 4 жыл бұрын

Thanks a lot Jon .. that was amazing and great job. Well done 👏

@sarinamacklin3577 4 жыл бұрын

Easy to follow, use and adopt. Thank you!

@nguyenhahuyenlinh358 2 жыл бұрын

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!

@70pjsmith 4 жыл бұрын

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

@siddharth.keshri 5 жыл бұрын

Hi Jon, You have very nice voice, thanks for helping

@billbonner5397 Жыл бұрын

GREAT tool! Thanks so much! - I wanted to change the font on the table of content to Calibri or some sans serif font. Any suggestions?

@user-tk2mg7eg3s 5 ай бұрын

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.

@AmyBowserRollins 5 жыл бұрын

Very cool, Jon. Thanks for sharing this.

@ExcelCampus 5 жыл бұрын

Hey Amy! Great to see you here. Thanks for your support! :-)

@anilmudakannavar79 5 жыл бұрын

Sir once again thanks a lot for sharing valuable knowledge, Its working Miracle Me.

@ExcelCampus 5 жыл бұрын

Thanks Anil! :-)

@jlettington 5 жыл бұрын

Great stuff Jon, thanks for sharing

@ExcelCampus 5 жыл бұрын

Thank you Jonathan! :-)

@rogerthat24 5 жыл бұрын

This is awesome. Thank you!

@adhorikahani1 5 жыл бұрын

excellent sir and thanks to teach us. I have got much skills from you

@hcandts 5 жыл бұрын

Excel-lent and simple Tops!!!

@ExcelCampus 5 жыл бұрын

Thanks Hans! :-)

@user-vr2uv3pv8b 11 ай бұрын

This TOC macro worked great for a while, but now I am getting an error box that says "Reference is not Valid." What is this error?

@alexrosen8762 4 жыл бұрын

Super useful! Thanks :-)

@baybay7898 Жыл бұрын

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.

@JP-hz5zb 5 жыл бұрын

Thanks very much, Jon!

@jeffreyhorton4669 8 ай бұрын

How can I get a copy of this template to learn and build my own?

@5gunnsnz 2 жыл бұрын

Doesn't work on Mac - Runtime Error 1004: Method "Autofilter" of Object "Range" Failed.. Any clues how to fix?

@katherinemcconnell7568 3 жыл бұрын

Thanks Jon, when in the worksheet is there a formula that can be added that takes you back to the TOC please?

@timcraven3543 3 жыл бұрын

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!

@McFadedevaded 5 жыл бұрын

Great! When i am looking for VBA code seems like everything is clear... but couldnot create by myself :)

@ExcelCampus 5 жыл бұрын

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! :-)

@Simi0102 4 жыл бұрын

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.

@MooChimp 9 ай бұрын

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

@benlumumbakhayr6635 3 жыл бұрын

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

@mikaelthornblad578 5 жыл бұрын

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)

@rakotondrasoahary5795 3 жыл бұрын

Do you have the steps on how to create table contents, please?

@VeloChill 5 жыл бұрын

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

@vidvicar 4 жыл бұрын

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?

@vidvicar 4 жыл бұрын

The error message appears when I click on the hyperlink in that row.

@user-lp2gb3vl3t Жыл бұрын

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. = )

@ExcelCampus Жыл бұрын

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/?

@CrissieLuckey 5 жыл бұрын

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?

@MohAboAbdo 5 жыл бұрын

Thanks ... Thanks ... T

@deirdreleigh4484 5 жыл бұрын

Bit pernickety I know but is it possible to change the font in the TOC (I have changed the text to arial but it reverts to the default on use).

@ExcelCampus 5 жыл бұрын

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.

@deirdreleigh4484 5 жыл бұрын

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

@ricos1497 5 жыл бұрын

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

@ginasmith9955 5 жыл бұрын

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.

@vturn1963 5 жыл бұрын

Hi Jon. The link seems to be missing to download the file.

@ExcelCampus 5 жыл бұрын

Thanks vturn. I updated the description. Here is the link. www.excelcampus.com/vba/table-of-contents-automatic-update/

@kweysi 5 жыл бұрын

Great work Jon, what happens if a worksheet is hidden or deleted??

@ExcelCampus 5 жыл бұрын

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! :-)

@kweysi 5 жыл бұрын

Thanks for the response it was helpful

@krishmaina 5 жыл бұрын

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.

@readywhen 4 жыл бұрын

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.

@1gopalakrishnarao 5 жыл бұрын

Thank you. I am trying this technique, but it is not working. Please help.

@IHACKER316 3 жыл бұрын

enable macros

@CarlosSanchezLemus 5 жыл бұрын

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.

@ExcelCampus 5 жыл бұрын

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.

@CarlosSanchezLemus 5 жыл бұрын

Excel Campus - Jon, Thank you a lot!!! Best regards. CS

@suhailnbd 5 жыл бұрын

NYC vedio Jon .how can I download this excel file.

@ExcelCampus 5 жыл бұрын

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/

@suhailnbd 5 жыл бұрын

@@ExcelCampus Thanks Jon