How to create table of contents for all sheets and updates automatically?
Supposing, you have a workbook which contains hundreds of worksheets, to navigate to a specific sheet among multiple sheets may cause headache for most of us. In this case, to create a table of contents for these worksheets may help you to go to the certain sheet quickly and easily. This tutorial will talk about how to create a table of contents for all sheets and update the table automatically when inserting, deleting or changing the sheet names as well.
Create a table of contents for all sheets and updates automatically with formulas
Create a table of contents for all sheets and updates automatically with a VBA code
Create a table of contents for all sheets and updates automatically with a powerful feature
Create a table of contents for all sheets and updates automatically with formulas
In Excel, you can create a table of contents by defining a range name, and referencing the name in a formula, please do with the following steps:
1. Insert a new sheet before all sheets where you want to create the table of contents and rename it as you need.
2. Then, click Formula > Define Name, see screenshot:
3. In the New Name dialog box, specify a name Sheetlist into the Name box, you can change it to your own, and then enter this formula: =GET.WORKBOOK(1)&T(NOW()) into the Refers to textbox, see screenshot:
4. Then, click OK button to close the dialog box.
5. Now, please go to the new sheet where you want to create a table of contents, and then enter the following formula into a blank cell:
6. And then drag the fill handle down to the cells until blank cells are displayed, and all sheet names (including the hidden sheets) of the current workbook have been listed as below screenshot shown:
7. Then, you should create hyperlinks for the table contents, please use the below formula:
8. Now, when clicking the hyperlink text, it will go to that sheet at once. And if you insert a new sheet, delete a sheet or change the sheet name, the table contents will be updated automatically.
- 1. With this method, all hidden sheets are displayed into the table contents as well.
- 2. You should save the file as Excel Macro-Enabled Workbook format, in this way, when you open this file next time, the formulas can work well.
Create a table of contents for all sheets and updates automatically with a VBA code
Sometimes, you needn’t display the hidden sheets in the table contents. To solve this problem, the following VBA code can do you a favor.
1. Insert a new sheet before all sheets where you want to create the table of contents and rename it as you need. Then, right click the sheet tab, and choose View Code from the context menu, see screenshot:
2. In the opened Microsoft Visual Basic for Applications window, copy and paste the below code into the sheet code window:
VBA code: Create a table of contents for all sheets and updates automatically
Private Sub Worksheet_Activate()
‘Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
If xWsh.Name <> xStrWShName Then
If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
Me.Range(xStrTCHeader).Offset(xI).Value = xI
xI = xI + 1
End If
End If
Next
Application.ScreenUpdating = True
End Sub
3. Then, press F5 key to run this code, and the table contents are created at once in the new worksheet, all hidden sheets will not display in the table contents, see screenshot:
4. Form now on, when deleting, inserting or renaming sheet, the table contents will be updated dynamically.
Create a table of contents for all sheets and updates automatically with a powerful feature
If you have Kutools for Excel, its Navigation pane can help you list all sheet names into a left pane vertically, and navigate to the specific sheet quickly and easily.
After installing Kutools for Excel, please do as this:
1. Click Kutools > Navigation, see screenshot:
2. In the expanded Navigation pane, click Workbook & Sheet icon, all opened workbooks are listed in the top list box, and all visible sheets within the workbook are displayed in the bottom list box, see screenshot:
3. And now, you can go to the sheet just by clicking the sheet name from the left pane. And when you delete, insert or rename a sheet, the sheet list in the pane will be updated dynamically.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!