excel的索引目录怎样制作

excel的索引目录怎样制作

按下组合键Alt+F11打开VBA编辑器，单击左边工程管理器，插入模块，在模块1中输入如下代码“

Sub 生成目录()
    Dim i, x As Integer
    Dim Sht1 As Worksheet
    Dim Str1 As String
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "目录" Then
            Set Sht1 = Sheets(i)
            Sht1.Move before:=Sheets(1)
            GoTo lp
        End If
    Next
lp:
    If i = Sheets.Count + 1 Then
        Set Sht1 = Sheets.Add(Sheets(1))
        Sht1.Name = "目录"
    End If
    With Sht1
        .UsedRange.Clear
        For i = 2 To Sheets.Count
            Str1 = Sheets(i).Name
            .Cells(i, 1) = Str1
            .Hyperlinks.Add anchor:=.Cells(i, 1), Address:="", SubAddress:=Str1 & "!A1", TextToDisplay:=Str1
        Next
    End With
End Sub

运行该段代码或运行宏“生成目录”即可生成索引目录。