vba 生成excel目录

您可以使用以下VBA代码在Excel中生成目录：

Sub GenerateTableOfContents()
    Dim sh As Worksheet
    Dim tocSheet As Worksheet
    Dim i As Long
    Dim j As Long
    Dim tocRow As Long
    
    Set tocSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    tocSheet.Name = "Table of Contents"
    tocSheet.Range("A1").Value = "Table of Contents"
    tocRow = 3
    
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> tocSheet.Name Then
            tocSheet.Hyperlinks.Add Anchor:=tocSheet.Cells(tocRow, 1), _
                Address:="", _
                SubAddress:="'" & sh.Name & "'!A1", _
                TextToDisplay:=sh.Name
                
            tocRow = tocRow + 1
            
            For i = 1 To sh.OutlineLevels.Count
                If sh.OutlineLevels(i) = 1 Then
                    tocSheet.Cells(tocRow, 2).Value = sh.Cells(i, 1).Value
                    tocSheet.Cells(tocRow, 2).IndentLevel = 1
                    tocSheet.Hyperlinks.Add Anchor:=tocSheet.Cells(tocRow, 1), _
                        Address:="", _
                        SubAddress:="'" & sh.Name & "'!" & sh.Cells(i, 1).Address, _
                        TextToDisplay:=sh.Cells(i, 1).Value
                        
                    tocRow = tocRow + 1
                End If
                
                If sh.OutlineLevels(i) = 2 Then
                    tocSheet.Cells(tocRow, 2).Value = sh.Cells(i, 1).Value
                    tocSheet.Cells(tocRow, 2).IndentLevel = 2
                    tocSheet.Hyperlinks.Add Anchor:=tocSheet.Cells(tocRow, 1), _
                        Address:="", _
                        SubAddress:="'" & sh.Name & "'!" & sh.Cells(i, 1).Address, _
                        TextToDisplay:=sh.Cells(i, 1).Value
                        
                    tocRow = tocRow + 1
                End If
            Next i
            
            tocSheet.Cells(tocRow, 1).Value = ""
            tocRow = tocRow + 1
        End If
    Next sh
    
    tocSheet.Columns("A:B").AutoFit
    tocSheet.Activate
End Sub

此代码会创建一个名为“Table of Contents”的新工作表，并将所有现有工作表的名称和其大纲级别1和2的单元格添加到该表中，同时在目录中创建超链接，以便单击目录中的条目后可以快速跳转到相应的工作表和单元格。