如何為所有工作表創建目錄並自動更新？

假設您有一個包含數百個工作表的工作簿，要在多個工作表中導航到特定的工作表可能會讓我們大多數人感到頭疼。在這種情況下，為這些工作表創建目錄可以幫助您快速輕鬆地跳轉到某個工作表。本教程將介紹如何為所有工作表創建目錄，並在插入、刪除或更改工作表名稱時自動更新目錄。

使用公式為所有工作表創建目錄並自動更新

使用 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. 從現在開始，當刪除、插入或重命名工作表時，目錄內容將動態更新。