Рет қаралды 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