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

工作中，如果數(shù)據(jù)量大工作表多文件，查看數(shù)據(jù)就需要來(lái)回切換工作表，操作很不便。于是就有了目錄索引。網(wǎng)上Excel目錄索引教程有兩種，都只實(shí)現(xiàn)了單向跳轉(zhuǎn)。

第1種，目錄只存在“目錄”工作表里：?jiǎn)螕裟夸洠袚Q到需要的工作表。

缺點(diǎn)：

只是單向跳轉(zhuǎn)，無(wú)法從工作表返回目錄，工作表之間的切換仍然很不方便，需要單擊工作表標(biāo)簽才能切換。

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



第2種，目錄存在所有工作表里：?jiǎn)螕裟夸洠梢苑奖愕脑诟鱾€(gè)工作表中切換。

缺點(diǎn)：

目錄擠占頁(yè)面空間。

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


今天要分享給大家的是做可以雙向跳轉(zhuǎn)、自動(dòng)更新的目錄索引：

單擊目錄，跳轉(zhuǎn)到工作表，雙擊工作表單元格，跳轉(zhuǎn)到目錄

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

不但能快速實(shí)現(xiàn)工作表之間的切換，目錄也不擠占頁(yè)面空間。

效果如下：


制作過(guò)程：

第一步：生成工作表目錄

（1）輸入代碼

按Alt+F11打開VBA編輯器，然后在左側(cè)工程資源管理器窗口，雙擊EXCEL對(duì)象里面的“Sheet1(目錄)”，在右側(cè)的代碼窗口輸入如下代碼（加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 '逐個(gè)獲取工作表名稱

Next i

End Sub

單引號(hào)'用于代碼注釋，其后的信息在VBA窗口里會(huì)顯示為綠色，不會(huì)作為代碼執(zhí)行。


解析：

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

（2）生成目錄

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



第二步：目錄跳轉(zhuǎn)到各工作表

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

Private Sub worksheet_SelectionChange(ByVal target As Range)

Dim sht As Worksheet

If target.Count > 1 Then Exit Sub '單擊多個(gè)單元格不執(zhí)行代碼

On Error Resume Next '碰到錯(cuò)誤繼續(xù)執(zhí)行

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

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

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

End If

End Sub

解析：

SelectionChange表示點(diǎn)擊單元格。上述代碼意思：?jiǎn)螕裟夸浟兄械哪硢卧窬蜁?huì)跳轉(zhuǎn)到以該單元格值命名的工作表。為了排除錯(cuò)誤跳轉(zhuǎn)，代碼還設(shè)置了單擊多個(gè)單元格不跳轉(zhuǎn)、如果不存在以單元格值命名的工作表也不跳轉(zhuǎn)等規(guī)則。




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

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

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

Worksheets("目錄").Activate

End Sub






解析：

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






Ok，現(xiàn)在我們就實(shí)現(xiàn)了雙向跳轉(zhuǎn)的全自動(dòng)目錄索引創(chuàng)建。

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


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