通過下方步驟使用按鈕結(jié)合VBA代碼可以將Excel工作簿里面的所有工作表獲取并生成如下圖所示目錄列表，并且可以單擊。

Step01:鼠標(biāo)依次單擊【開發(fā)工具】——【Visual Basic】,打開VBA編程窗口

Step02:如下圖所示，再新打開的窗口左側(cè)雙擊Sheet1,右側(cè)窗口輸入代碼（step03提供）。

Step03:右側(cè)窗口復(fù)制輸入的VBA代碼如下：

        Option Explicit

        Sub Create_TOC()

        Dim wbBook As Workbook

        Dim wsActive As Worksheet

        Dim wsSheet As Worksheet

        Dim lnRow As Long

        Dim lnPages As Long

        Dim lnCount As Long

        Set wbBook = ActiveWorkbook

        With Application

            .DisplayAlerts = False

            .ScreenUpdating = False

        End With

        'If the TOC sheet already exist delete it and add a new

        'worksheet.

        On Error Resume Next

        With wbBook

            .Worksheets("目錄").Delete

            .Worksheets.Add Before:=.Worksheets(1)

        End With

        On Error GoTo 0

        Set wsActive = wbBook.ActiveSheet

        With wsActive

            .Name = "目錄"

            With .Range("A1:B1")

                .Value = VBA.Array("工作表名稱", "順序 # - # 包含多少頁")

                .Font.Bold = True

            End With

        End With

        lnRow = 2

        lnCount = 1

        'Iterate through the worksheets in the workbook and create

        'sheetnames, add hyperlink and count & write the running number

        'of pages to be printed for each sheet on the TOC sheet.

        For Each wsSheet In wbBook.Worksheets

            If wsSheet.Name <> wsActive.Name Then

                wsSheet.Activate

                With wsActive

                    .Hyperlinks.Add .Cells(lnRow, 1), "", _

                    SubAddress:="'" & wsSheet.Name & "'!A1", _

                    TextToDisplay:=wsSheet.Name

                    lnPages = wsSheet.PageSetup.Pages().Count

                    .Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages

                End With

                lnRow = lnRow + 1

                lnCount = lnCount + 1

            End If

        Next wsSheet

        wsActive.Activate

        wsActive.Columns("A:B").EntireColumn.AutoFit

        With Application

            .DisplayAlerts = True

            .ScreenUpdating = True

        End With

        End Sub

Step04:關(guān)閉打開的VBA編輯窗口，鼠標(biāo)依次單擊【開發(fā)工具】——【插入】——【按鈕】，工作表格的工作區(qū)拖動(dòng)繪制后會(huì)自動(dòng)彈出“指定宏”對話框，選擇后，單擊確定按鈕即可。

最后，單擊按鈕即可自動(dòng)生成一個(gè)新的并且?guī)�目錄的工作�?nbsp;