文件中如果有多張工作表，切換就成了問題。尤其是提交給老板的文件更應該考慮工作表的快速切換。通常采用目錄索引的方式進行工作表的切換。只不過常見的目錄索引做法存在單向跳轉，或者目錄擠占頁面的情況。本篇教程，則通過最簡單的3步操作——都是復制粘貼給出的代碼，實現自動更新、雙向跳轉，并且不擠占工作頁面的目錄。

工作中，如果數據量大工作表多文件，查看數據就需要來回切換工作表，操作很不便。于是就有了目錄索引。網上Excel目錄索引教程有兩種，都只實現了單向跳轉。

第1種，目錄只存在“目錄”工作表里：單擊目錄，切換到需要的工作表。

缺點：

只是單向跳轉，無法從工作表返回目錄，工作表之間的切換仍然很不方便，需要單擊工作表標簽才能切換。

目錄不會自動更新。增加新的工作表后，需要在目錄里手動填充公式才能更新目錄。



第2種，目錄存在所有工作表里：單擊目錄，可以方便的在各個工作表中切換。

缺點：

目錄擠占頁面空間。

目錄不會自動更新。增加新的工作表后，需要在目錄里手動填充公式才能更新目錄。


今天要分享給大家的是做可以雙向跳轉、自動更新的目錄索引：

單擊目錄，跳轉到工作表，雙擊工作表單元格，跳轉到目錄

增加、刪除了工作表后，目錄自動更新，不用手動填充公式

不但能快速實現工作表之間的切換，目錄也不擠占頁面空間。

效果如下：


制作過程：

第一步：生成工作表目錄

（1）輸入代碼

按Alt+F11打開VBA編輯器，然后在左側工程資源管理器窗口，雙擊EXCEL對象里面的“Sheet1(目錄)”，在右側的代碼窗口輸入如下代碼（加QQ群:920864360可以下載代碼）:

Private Sub worksheet_activate()

Dim i As Integer, ss As String

Range("a:a").Clear '清空A列

For i = 1 To Worksheets.Count

Range("A" & i).Value = Worksheets(i).Name '逐個獲取工作表名稱

Next i

End Sub

單引號'用于代碼注釋，其后的信息在VBA窗口里會顯示為綠色，不會作為代碼執行。


解析：

Activate表示工作表激活。上述代碼表示：一旦從其他工作表跳轉到“目錄”工作表，就執行代碼，逐個獲取工作表的名稱，在目錄工作表的 A列生成目錄。

（2）生成目錄

首先單擊任何一個非目錄工作表，然后再單擊切換到“目錄”工作表，完成目錄生成。



第二步：目錄跳轉到各工作表

打開VBA編輯器，雙擊EXCEL對象里面的“Sheet1(目錄)”，在右側的代碼窗口輸入如下代碼:

Private Sub worksheet_SelectionChange(ByVal target As Range)

Dim sht As Worksheet

If target.Count > 1 Then Exit Sub '單擊多個單元格不執行代碼

On Error Resume Next '碰到錯誤繼續執行

Set sht = Worksheets(target.Value) '查看是否存在以Target.Value命名的工作表

If Err.Number = 0 Then '如果錯誤為0，即工作簿存在以Target.Value命名的工作表，否則就不執行代碼

Worksheets(target.Value).Activate '激活該工作表

End If

End Sub

解析：

SelectionChange表示點擊單元格。上述代碼意思：單擊目錄列中的某單元格就會跳轉到以該單元格值命名的工作表。為了排除錯誤跳轉，代碼還設置了單擊多個單元格不跳轉、如果不存在以單元格值命名的工作表也不跳轉等規則。




第三步：由各工作表跳回目錄

打開VBA編輯器，然后在左側工程資源管理器窗口，雙擊EXCEL對象里面的“ThisWorkbook”，在右側的代碼窗口輸入如下代碼:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Worksheets("目錄").Activate

End Sub






解析：

SheetBeforeDoubleClick表示雙擊單元格。上述代碼意思：在任一工作表內雙擊單元格就跳轉到“目錄”工作表。






Ok，現在我們就實現了雙向跳轉的全自動目錄索引創建。

最后提醒大家一下，如果想在工作簿使用VBA代碼，除了EXCEL2003其他版本的都要保存為EXCEL啟用宏的工作簿“xlsm”格式哦！


好了，分享結束。方法步驟有不有清楚呢，還是親自動手試試效果吧！