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 & Subscribe to our Channel

Download the Excel File

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

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.

Adding a Macro to Create the Table of Contents

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

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.
   
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.”
   
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.
   
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! 🙂