43

Excel Table of Contents that Automatically Updates

Bottom Line: Learn to create a Table of Contents sheet that automatically updates whenever changes are made to sheets in an Excel workbook.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

Here's a link to the file I use in the video that includes the VBA macros.

Table Of Contents Auto Update Macro.xlsm (39.1 KB)

The following file will work with workbooks that contain Chart sheets.  These are NOT worksheets that contain charts.  Chart sheets are a special type of sheet that only contains a single chart that takes up the entire sheet.

Table Of Contents Auto Update Macro - Chart Sheets.xlsm (48.8 KB)

Chart Sheets cannot be selected with hyperlinks because they do not contain cells.  Therefore, this solution uses the Worksheet_FollowHyperlink event to select the Chart sheet.  All of the code is still contained in the shtTOC sheet module, and the sheet can be copied to other workbooks.

Create Automatic Updates to Your Table of Contents in Excel

A Table of Contents is a simple yet extremely useful feature in any Excel workbook that contains more than a few sheets.  This one sheet can provide a clickable list of your worksheet labels that link directly to their corresponding sheets.

This post will demonstrate how to create a Table of Contents (TOC) that updates automatically.  The TOC will display an accurate list when you add, remove, or change the names of sheets in the workbook.

Table of Contents Automatically Updates When Changes are made to Worksheets

Adding a Macro to Create the Table of Contents

In this example I use two macros to update the Table of Contents.

Macro to refresh the table of contents automatically

The first macro is the Worksheet_Activate event.  This macro will run every time the user selects the Table of Contents sheet.  The code is stored in the sheet's code module, and will only run when that particular sheet is activated (selected).

The code in the Worksheet_Activate calls the second macro (TOC_List).  This macro actually recreates the entire Table of Contents on the sheet.

Alternatively, you could put all the code in the TOC_List macro in the Worksheet_Activate event.  I just kept them separate in case you want to use a different macro for the TOC, like the TOC Gallery macro I mention below.

You just have to change the Call line in the Worksheet_Activate macro to call a different macro.  The macro can also be stored in the same code module for the TOC sheet.

Use it in Your Own Workbooks

This solution is very easy to implement in your own workbooks.

All you have to do is copy the TOC tab (provided in the file download above) to your workbook.  All of the code travels with the sheet because it is stored in the sheet's code module.

Here's how you can copy the sheet to a different workbook.  It's always a good idea to save your workbook before adding this sheet that contains VBA code.

  1. Right-click on the Table of Contents tab, and then choose “Move or Copy…” in the menu that appears.
    Right click table of content choose move or copy
  2. Choose the open workbook that you would like to copy the Table of Contents to.  In my example, I am using a workbook called “200 Sheets.”
    Choose the workbook to copy to
  3. Choose the sheet you want the Table of Contents to appear in front of (usually the first sheet since Tables of Contents are normally found at the beginning).  Then check the “Create a copy” checkbox.  Then select OK.
    Copy Table of Contents to a new workbook
  4. The sheet will be copied into the workbook and the Worksheet_Activate event should automatically run to create the new table of contents.

That's all there is to it.  As with any workbook that contains macros, don't forget to save it as a macro-enabled file (.xlsm extension).

If you're new to VBA then checkout my free 3-part video series on getting started with macros & VBA.

VBA Code for Automatically Refreshing the Table of Contents

Here is the VBA code for both macros that you can copy and paste into your own workbook.  You can also find it in the example Excel file that I've provided above.

Sub TOC_List()
'Create Table of Contents on this TOC sheet

Dim ws As Worksheet
Dim wsTOC As Worksheet
Dim i As Long

  Application.ScreenUpdating = False
  
  'Set variables
  Const bSkipHidden As Boolean = False 'Change this to True to NOT list hidden sheets
  Const sTitle As String = "B2"
  Const sHeader As String = "B4"
  Set wsTOC = Me 'can change to a worksheet ref if using in a regular code module
  i = 1

  'Clear Cells
  wsTOC.Cells.Clear
  
  'Title
  With wsTOC.Range(sTitle)
    .Value = "Table of Contents"
    .Font.Bold = True
    .Font.Size = .Font.Size + 2
    
    'List header
    .Offset(2).Value = "#"
    .Offset(2, 1).Value = "Sheet Name"
    .Offset(2).Resize(1, 2).Font.Bold = True
  End With
  
  'Create TOC list
  With wsTOC.Range(sHeader)
  
    'Create list
    For Each ws In ThisWorkbook.Worksheets
        'Skip TOC sheet
        If ws.Name <> wsTOC.Name Then
          'Skipping hidden sheets can be toggled in the variable above
          If bSkipHidden Or ws.Visible = xlSheetVisible Then
            .Offset(i).Value = i
            wsTOC.Hyperlinks.Add Anchor:=.Offset(i, 1), _
                                  Address:="", _
                                  SubAddress:="'" & ws.Name & "'!A1", _
                                  TextToDisplay:=ws.Name
            i = i + 1
          End If
        End If
    Next ws
    
    'Turn filters off
    If wsTOC.AutoFilterMode Then
      wsTOC.Cells.AutoFilter
    End If
    
    'Apply filters
    .Resize(i, 2).AutoFilter
    
    'Formatting
    .Font.Italic = True
    
    'AutoFit
    .Resize(i, 2).Columns.AutoFit
  
  End With
  
  Application.ScreenUpdating = True
  
End Sub

Of course, if you already have a Table of Contents macro that you prefer, you can substitute that code for the TOC_List macro.

How to Skip Hidden Sheets

By default the TOC_List macro will include hidden sheets in the list.  However, I added some code to the macro that allows you to skip hidden sheets.  This is controlled by the bSkipHidden variable that can be set at the top of the macro.

Here is what that section of code looks like in the macro:

'Set variables
Const bSkipHidden As Boolean = False 'Change this to True to NOT list hidden sheets
  Const sTitle As String = "B2"

It is currently set to False, but you can change it to True to skip hidden sheets.  The Table of Contents list will only include the visible sheets in the workbook.  Since the macro runs automatically, the Table of Contents will always be updated to display visible sheets only.

Table of Contents Gallery

At the end of the video I show how this same technique can be used with my Table of Contents Gallery macro.  If you or your users are more visual-oriented, you might be interested in this solution.  Instead of just clickable text, this Table of Contents shows you a snapshot of each sheet in the workbook.  Each image is clickable and will take you directly to the corresponding sheet.

Just a note: This gallery image format does run slower than the list format I described earlier, especially if your workbook has a lot of sheets.

Event-Based Macros

As mentioned before, the Worksheet_Activate event runs any time the user activates the sheet that the code is in.  In other words, it's event based.  You can set up macros to run based on other events too, such as:

  • Whenever you open or close a workbook.
  • Changes are made to any cell, or a particular cell.
  • Pivot tables are refreshed.

There are a ton of uses for event macros that will help make your files easier to use.  Checkout my post and video on VBA Code Modules & How to Run Macros Based on User Events to learn more.

Professional and User Friendly

Adding a Table of Contents to your workbooks is super easy.  And the great thing is that this one feature has the tendency to impress other users (and hopefully your bosses as well!).  That's especially true of the gallery format because it looks like you did so much work!

If you would like to explore some other tips and tricks for making your job easier, making your data look more professional, and making your worksheets more user-friendly, check out my video on 5 uses for VBA macros at your job.

I hope that helps.  Please leave a comment below with any questions or suggestions.  Thank you! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 43 comments
Ron - October 10, 2018

John, could you please put the text of ALL of the macros in the body of the article so we can copy them. When I copied the one macro in the body, I got an error. When I opened the example file and copied all 3 of the macros there, it finally worked.

Reply
gopalakrishna rao - October 7, 2018

It is not working.I copied in the module. Error message and cursor is stopping at me.When I run the macros. Please help. I have to use this for many of my workbooks.Please help me.

Reply
Wiley Thomas - October 6, 2018

Nice one Jon – love the auto-update feature. FYI – I run a mac normally and ran into an error with the AutoFilter. I changed it to be a two-step process as follows:
‘Apply filters
.Resize(i, 2).Select
Selection.AutoFilter

Probably a function of the mac version of Excel always being the step-child 🙂

Thanks again

Reply
Blane - October 1, 2018

Jon,

I got the TOC for worksheets to work. The TOC Gallery Macro would not run when I copied to my existing Excel Workbook. Do I need Tab Hound to make this work?

Thanks,
Blane

Reply
Gwen - September 30, 2018

Good article and well presented!

I will be using this..

Reply
ANDREW - September 29, 2018

Fabulous idea.

Rather interestingly when I stuck this in my Mac spreadsheet it works but I get “Runtime Error 1004 – Method ‘Autofilter’ of object ‘Range failed”. Wondering what it means and how to fix it?

Reply
    Jon Acampora - October 1, 2018

    Thanks Andrew.

    I have not tested it on the Mac version yet. A lot will probably depend on which version of Excel you are using on the Mac. If you are on Office 365, you might want to make sure you are updated to the latest version. Microsoft has been making some updates to VBA on the Mac lately. Here’s an article with more info on the new VB Editor for Mac.

    If that doesn’t work then you can comment out the lines of code for the autofilters. Just put apostrophes at the beginning of all the following lines, or delete the lines all together. This will ensure those lines don’t run. The TOC will still be created, you just won’t have the autofilters turned on.

    'Turn filters off
    'If wsTOC.AutoFilterMode Then
    '  wsTOC.Cells.AutoFilter
    'End If
        
    'Apply filters
    '.Resize(i, 2).AutoFilter

    I hope that helps.

    Reply
Shobi Imran - September 29, 2018

Nice Article, thanks for sharing, Keep up the good work!

Reply
Kim - September 28, 2018

Hello Jon…this is awesome!
I’m new to VBA, but I can only imagine that there must be a way to create a link back to the table of contents page once you click on a tab????? Just think that would be useful 🙂

Reply
    Jon Acampora - September 28, 2018

    Hey Kim,
    Great question! Yes, we can absolutely create a backlink to the TOC. My Tab Hound add-in has this feature to create the backlink, but requires you add it to each sheet manually.

    You can also do this manually by creating the link to the TOC on one sheet, then copy/paste it to all the other sheets. You can do this all at one time by first selecting multiple sheets (select the first sheet, hold shift, select the last sheet), then paste the hyperlinked cell to a blank cell. The only problem is that same cell has to be blank on all of the sheets you have selected. So if you know cell A2 is blank on all sheets, then you can use this technique. If not, then you have to manually go through each sheet.

    This process can be automated with a macro. It gets a bit tricky, because you don’t want to override data on existing sheets. However, I’ll post a solution to this in the future as well. I was actually just thinking about a few ways to do this.

    I hope that helps. Thanks again and have a nice day! 🙂

    Reply
Russell - September 28, 2018

Jon,

As always this is just great stuff! Is there a way to not show hidden worksheets in the table of contents?

Reply
    Jon Acampora - September 28, 2018

    Hi Russell,
    Great question! Yes, I updated the code to allow you to toggle including hidden sheets in the list.

    In the Set Variables section you will see the following line of code.

    Const bSkipHidden As Boolean = False 'Change this to True to NOT list hidden sheets

    You can change that to True to skip hidden sheets. It will then look like the following

    Const bSkipHidden As Boolean = True 'Change this to True to NOT list hidden sheets

    When set to True you should only see visible sheets. Hidden and VeryHidden sheets will not be listed in the TOC.

    I hope that helps. Thanks again and have a nice weekend! 🙂

    Reply
Deirdre - September 28, 2018

Hi, I’ve started to look at several of your recent articles and this one is great. I’m currently working on a large spreadsheet so have added it (no problem). My only niggle is that I prefer Arial font and this comes through to my sheet partly in Arial and partly in Calibri? I’d like it to be in the same font, and smaller so I can see more sheets listed. If I simply save the sheet it reverts on next use, so I guess I’d need to add this to the code (but I have no idea how to do that!).

Reply
    Jon Acampora - September 28, 2018

    Hi Deirdre,
    I’m copying my reply from the YouTube comments here so others see it.

    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.

    You can copy/paste the code on the page above, both macros. Or you can download the file and use that code in the sheet module.

    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.

    Reply
Sandeep Kothari - September 28, 2018

Dear Jon
Thanks, thanks & thanks for the wonderful video. I have following questions regarding TOC code:

1) Why only private sub is used for worksheet click events? why public sub is not used?
2) Why do click event subs only use Call Functions & there is no other code therein?
3) Why is TOC sub public & not private?
4) The workbook that I downloaded from the link provided in your blog post did not open. Displays error message. Pl. check.

Reply
Mark Baxter - September 28, 2018

Hi Jon,

Great code and really useful as your stuff always is. How would you modify the code so that it didn’t list any hidden worksheets?

Cheers

Bax

Reply
    Jon Acampora - September 28, 2018

    Hey Bax,

    Great question! I just updated the code to include a toggle to skip hidden sheets.

    In the Set Variables section you will see the following line of code.

    Const bSkipHidden As Boolean = False 'Change this to True to NOT list hidden sheets

    You will just change that line to:

    Const bSkipHidden As Boolean = True

    The If statement in the loop below will then check if the sheet is visible OR the toggle is on (bSkipHidden = TRUE). If both or EITHER are true then it will list the sheet. If BOTH are false then it will not list the sheet.

    I hope that helps. Thanks again and have a nice weekend! 🙂

    Reply
Richard - September 27, 2018

As always, your skills and solutions are outstanding.
Your stuff makes me look good to my boss. . . . but you always get credit.
🙂

Reply
    Jon Acampora - September 28, 2018

    Hi Richard,

    Thank you so much for the nice feedback. I really appreciate your support. And happy to hear you’re impressing the boss with this. I love it!

    Have a great weekend! 🙂

    Reply
Michael Noble - September 27, 2018

Jon,

2 questions.

1) What advantage do you have by calling the sub with Worksheet_Activate compared to just placing the code in that subroutine itself?

2) By storing the code in the worksheet rather than a module, is it then possible to store personal macros in that sheet as well, so you can easily access them by just moving a copy of the sheet to a new workbook?

I have had several occasions where I used a personal workbook for macros, then it crashed and what I mess, I went back to keeping my code in notepad. However, I am constantly looking for a better way to store it.

Thank you.
MichaelGNoble

Reply
    Jon Acampora - September 28, 2018

    Hi Michael,

    Great questions! Here are my answers.

    1) The only advantage is that it is easier to plug in your own TOC macro, or use different ones for different workbooks. You can store all of your different TOC macros in the sheet module, then just change the call line.

    If you want to run the TOC Gallery macro that is included in the code module instead, then you would just change the call line to the following.

    Call TOC_Gallery

    This just makes it a little more versatile. Otherwise there is no advantage. You could just put all the code in the Activate event and it would work the same.

    One other small advantage is that you can just comment out that single Call line if you don’t want the macro to run. Not a big deal, but sometimes with events we want to temporarily disable them when developing or debugging.

    2) Yes, you could store other macros in that sheet, or any sheet module. Some developers prefer this method because you can just copy sheets to other workbooks and the sheet becomes somewhat of a self contained application. A lot depends on the purpose of the macro.

    In general, I prefer to store code in code modules instead of sheet modules. It is easier to see that there is VBA code in the workbook by looking in the Modules folder in the VB Editor.

    When code is stored in sheet modules you end up having to click through each module to see if it contains code, or using Ctrl+F to find code.

    In regards to storing code in your Personal Macro Workbook (PMW), I recommend adding the file’s folder to your backup solution. The file is stored in the XLSTART folder, and should definitely be included in your backup plan on regular intervals. Here is a link to a video that explains how to find the XLSTART folder on your computer.

    I hope that helps. Thanks again and have a nice day! 🙂

    Reply
ABRAHAM LEE - September 27, 2018

Thank you so much.
This great tool is really help me.
I AM SO HAPPY.

Reply
    Jon Acampora - September 27, 2018

    Awesome! Thanks Abraham! 🙂

    Reply
Nick Montanari - September 27, 2018

Can this macro be added to the ribbon or a personal macro workbook? I’d like to access in the ribbon like you showed in your other article, or does it have to be copied and pasted over each time? I got the gallery macro working in the ribbon. Thanks for sharing Jon, amazing stuff as always.

Reply
    Jon Acampora - September 27, 2018

    Hi Nick,

    The macro to create the regular list TOC can be added to your PMW and called from the ribbon.

    The Worksheet_Activate event that does the auto refresh will need to be in the workbook that contains the TOC. Well, that’s not entirely true. You could use App Events in your PMW or an add-in to run this on any open file that contains a TOC sheet.

    I’m not sure I’d recommend that, as the code would be running in the background each time you select a different sheet. It also has the potential to run on workbooks you don’t want it to, depending on how you program the event to run. You could add additional conditions so it only runs on sheets with some hidden text string in a specific cell, or something to make sure it’s the workbook you want to run it on.

    I hope that helps. Let me know if you have questions.

    Reply
Nick Montanari - September 27, 2018

Can you add this macro to the ribbon in “my macros” tab or your personal macro workbook that you showed how to create in another article? I’d like to add it to the ribbon rather than having to copy and paste if possible. Thanks for sharing, Jon. This is awesome as usual.

Reply
Randall Ader - September 27, 2018

Hi Jon,

Thanks for the great article and insights to the complexities of Excel macros and VBA code.

I did what the article said to do, whether copy/paste the code or download the example and copy the tab to other workbooks, and it works great except for one sheet.

The tab gets copied ok, but when it runs on activate, I get:
Run-time error ‘-2147418113 (9000ffff)’;
Automation error
Catastrophic failure

The code stops on this line: For Each ws In ThisWorkbook.Worksheets
Looking at the watch for ws it shows a value of Nothing.

I looked at all the characters in the tabs names to see if there were any questionable characters, compared it to your example using the odd characters (ie: & and – ), but that didn’t make a difference.

My sheet only has 43 tabs, so I doubt that’s the issue. Only other thing that comes to mind is maybe the tabs character count.

Any ideas?

Reply
    Jon Acampora - September 27, 2018

    Hi Randall,

    Does the file contain any Chart sheets? Not sheets that contain charts, but the Chart sheets where the chart takes up the entire page.

    That could be causing it, although the macro should just bypass those sheets.

    I’m not sure what else would be causing that.

    Reply
      Raandall Ader - September 28, 2018

      No charts sheets. Some conditional formatting to dress them up, but nothing out of the ordinary.

      When the macro runs, it creates the Title and List Header properly. Stepping thru the macro everything is good until the first pass thru the For Each statement.

      I’m going to copy the sheets to another workbook, strip out the confidential data, and try with blank sheets next, keeping the tab names to see what happens.

      Reply
      Raandall Ader - September 28, 2018

      Hello again Jon,

      I think I’ve found the problem, and it’s not code related. My sheet has grown over many years and some oddities have crept in.

      So to troubleshoot, I cleared out all the data, kept the tabs and ran the macro. Same errors. Then I deleted all but two tabs, the TOC being generated and a new blank tab. Ran the macro and same error.

      While in the VB editor I noticed when I deleted the workbook tabs, the corresponding sheet in the VB window also disappeared. After removing all the tabs but the two, I still had Sheet1 thru Sheet42, with an icon that looked like the XL symbol. These weren’t hidden tabs (hidden tabs do get TOC’ed though – good to know) or anything visible. I also couldn’t find a way to get rid of them.

      So final solution was to create a new workbook, copy the 43 tabs from the working sheet, copy your TOC macro, and watch the magic happen. Interesting to note that the good tabs in the unworkable sheet were listed as Sheet43 thru Sheet89. After copying the good tabs to the new workbook, the tabs kept the same Sheet43-89 names. It doesn’t throw off the macro in any way. Nearest I can figure is that odd Sheets may have crept in from 2003 format converted to the 2016 over the many years.

      If you would like to investigate this issue, I have the two tab unworkable sheet and screen shots of the odd Sheet numbering issue. Email me if your interested.

      Thank you for a great tool and I look forward to taking your classes in the near future.

      Reply
        Jon Acampora - September 28, 2018

        Hi Raandall,

        That’s interesting. They could be old Macro sheets. Or maybe just something that is corrupt.

        The Sheet43 to Sheet89 you see in the VB Editor are the sheets’ code names. These can be used as sheet references in our code, instead of referencing the sheet by it’s tab name. There are some advantages to this. I explain more about those in The VBA Pro Course.

        The code names should not change when you copy them to new workbooks, unless the workbook already has an existing sheet with the code name. You’ll see that I renamed the TOC sheet’s code name to shtTOC to keep it unique.

        I also just updated the code to change that reference to a variable, so people can copy the code into their workbook without having to copy the entire sheet. This is mostly if they want to keep the formatting of their existing workbook. Cell styles do travel with the sheets, and some people don’t use the current/modern cell styles.

        I hope that helps. Thanks again and have a nice weekend! 🙂

        Reply
Patty B - September 27, 2018

This is the most amazing thing I’ve ever seen in Excel. I have spent forever creating my own Contents pages and hyperlinking every tab. Wow. Mind still blown. Thank you again and again Jon

Reply
    Jon Acampora - September 27, 2018

    Thanks so much Patty! I’m so happy to hear you will be using this. 🙂

    Reply
sandeep kothari - September 27, 2018

Jon, You never cease to amaze me with your VBA codes & excel skills! Wish you the best with your courses.

Reply
    Jon Acampora - September 27, 2018

    Thanks Sandeep! I really appreciate your support. 🙂

    Reply
Bree - September 27, 2018

Hi Jon! I have enjoyed several of your videos. With each tutorial I watch my skills improve and I continue to look for ways to add my new knowledge. I was very excited when I saw this video on the Table of Contents Gallery; however when I try to open the spreadsheet; I get a notification from Excel saying that “Excel found unreadable content in ‘Table-of-Content-Gallery.xlsm'”. I tried to recover the contents of the workbook, but to no avail. I am using Excel 2010. Any directions that you could provide would be greatly appreciated.

Reply
    Jon Acampora - September 27, 2018

    Hi Bree,
    I’m not sure what would be causing that error. It should work in Excel 2010. The file on this page also contains the code for the TOC Gallery macro. It’s in the same sheet module as the other code. You can try that code as well.

    Reply
Mark Dodson - September 27, 2018

Great tool! Thanks for explaining it too.

Reply
Marc R - September 27, 2018

Thanks Jon. This TOC code will be very helpful in our current workbooks, some of which have dozens of tabs. Great explanation as always. Your blog continues to be one of my go to locations for learning more about excel and VBA and to get great tips and tricks.

Reply
    Jon Acampora - September 27, 2018

    Thank you Marc! Happy to hear you will be putting this to good use. I really appreciate your support. 🙂

    Reply

Leave a Reply: