編按：

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

工作中，如果數(shù)據(jù)量大工作表多文件，查看數(shù)據(jù)就需要來回切換工作表，操作很不便。于是就有了目錄索引。

網(wǎng)上Excel目錄索引教程有兩種，都只實(shí)現(xiàn)了單向跳轉(zhuǎn)：

第1種，目錄只存在“目錄”工作表里。

單擊目錄，切換到需要的工作表。

缺點(diǎn)：

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

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

第2種，目錄存在所有工作表里。

單擊目錄，可以方便的在各個(gè)工作表中切換。

缺點(diǎn)：

◎目錄擠占頁面空間。

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

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

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

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

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

效果如下：

第一步：生成工作表目錄

（1）輸入代碼

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

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”格式哦！

下載：

本文配套的練習(xí)課件與代碼請(qǐng)加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

《 用GET.WORKBOOK函數(shù)實(shí)現(xiàn)excel批量生成帶超鏈接目錄且自動(dòng)更新》——典型的單向跳轉(zhuǎn)目錄

《Word自動(dòng)生成目錄很簡(jiǎn)單，3步搞定！》——了解一下WORD目錄也不錯(cuò)

《 Excel-VBA小白脫白記》——對(duì)VBA發(fā)憷的人很適合

版權(quán)：

原文：最快捷的Excel目錄制作：不用公式，只需3次復(fù)制粘貼

作者：夏雪

出處：http://www.ittribalwo.com/article/4469.html