Excel自动生成目录教程

通过下方步骤使用按钮结合VBA代码可以将Excel工作簿里面的所有工作表获取并生成如下图所示目录列表，并且可以单击。

Step01:鼠标依次单击【开发工具】——【Visual Basic】,打开VBA编程窗口

Step02:如下图所示，再新打开的窗口左侧双击Sheet1,右侧窗口输入代码（step03提供）。

Step03:右侧窗口复制输入的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:关闭打开的VBA编辑窗口，鼠标依次单击【开发工具】——【插入】——【按钮】，工作表格的工作区拖动绘制后会自动弹出“指定宏”对话框，选择后，单击确定按钮即可。

最后，单击按钮即可自动生成一个新的并且带目录的工作表 

说明：本示例代码是由微软官方提供，本站发布的目的是为了方便各位职场人士使用，

原文链接：将目录添加到工作簿 | Microsoft Learn