如何为所有工作表创建目录并自动更新？

假设您有一个包含数百个工作表的工作簿，在多个工作表中导航到特定的工作表可能会让我们大多数人头疼。在这种情况下，为这些工作表创建一个目录可以帮助您快速轻松地跳转到某个工作表。本教程将讨论如何为所有工作表创建目录，并在插入、删除或更改工作表名称时自动更新目录。

使用公式为所有工作表创建目录并自动更新

使用Kutools for Excel为所有工作表创建目录并自动更新

使用VBA代码为所有工作表创建目录并自动更新

使用公式为所有工作表创建目录并自动更新

在Excel中，您可以通过定义单元格名称并在公式中引用该名称来创建目录，请按照以下步骤操作：

1. 在所有工作表之前插入一个新工作表以创建目录，并根据需要重命名它。

2. 然后，点击“公式”>“定义名称”，请参见截图：

3. 在“新建名称”对话框中，指定一个名称“Sheetlist”到“名称”框中，您可以将其更改为自己的名称，然后在“引用位置”文本框中输入以下公式。

=GET.WORKBOOK(1)&T(NOW())

4. 然后，点击“确定”按钮关闭对话框。

5. 现在，请转到要创建目录的新工作表，然后在空白单元格中输入以下公式：

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")

6. 然后向下拖动填充柄直到显示空白单元格，当前工作簿中的所有工作表名称（包括隐藏的工作表）都已列出，如下图所示：

7. 然后，您应该为目录内容创建超链接，请使用以下公式：

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

8. 现在，当点击超链接文本时，它将立即跳转到该工作表。并且如果您插入新工作表、删除工作表或更改工作表名称，目录内容将自动更新。

使用Kutools for Excel为所有工作表创建目录并自动更新

如果您安装了“Kutools for Excel”，其“导航窗格”可以帮助您将所有工作表名称垂直列在左侧窗格中，并快速轻松地导航到特定工作表。

安装Kutools for Excel后，请按以下步骤操作：

1. 点击“Kutools”>“导航”，请参见截图：

2. 在展开的“导航”窗格中，点击“工作簿和工作表”图标，所有打开的工作簿都会列在顶部列表框中，而工作簿内的所有可见工作表则会显示在底部列表框中，请参见截图：

3. 现在，您只需点击左窗格中的工作表名称即可跳转到该工作表。当您删除、插入或重命名工作表时，窗格中的工作表列表将动态更新。

使用VBA代码为所有工作表创建目录并自动更新

有时，您不需要在目录内容中显示隐藏的工作表。为了解决这个问题，以下VBA代码可以帮到您。

1. 在所有工作表之前插入一个新工作表以创建目录，并根据需要重命名它。然后，右键单击工作表标签，并从上下文菜单中选择“查看代码”，请参见截图：

2. 在打开的“Microsoft Visual Basic for Applications”窗口中，复制并将以下代码粘贴到工作表代码窗口中：

VBA代码：为所有工作表创建目录并自动更新

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. 然后，按“F5”键运行此代码，目录内容将在新工作表中立即创建，所有隐藏的工作表将不会显示在目录内容中，请参见截图：

4. 从现在开始，当删除、插入或重命名工作表时，目录内容将动态更新。