Excel 自动根据Sheet名生成目录

来源：互联网发布：网络资讯免费资讯编辑：程序博客网时间：2024/04/28 01:06

以下方法转载自网络，以便大家共享

打开你要创建，目录的表格按Alt+F11在谈出的对话框中点插入选择模块然后把下面的代码复制到模块中

Sub mulu()
    On Error GoTo Tuichu
    Dim i As Integer
    Dim ShtCount As Integer
    Dim SelectionCell As Range

    ShtCount = Worksheets.Count
    If ShtCount = 0 Or ShtCount = 1 Then Exit Sub
    Application.ScreenUpdating = False
    For i = 1 To ShtCount
        If Sheets(i).Name = "目录" Then
            Sheets("目录").Move Before:=Sheets(1)
        End If
    Next i
    If Sheets(1).Name <> "目录" Then
        ShtCount = ShtCount + 1
        Sheets(1).Select
        Sheets.Add
        Sheets(1).Name = "目录"
    End If
    Sheets("目录").Select
    Columns("B:B").Delete Shift:=xlToLeft
    Application.StatusBar = "正在生成目录…………请等待！"
    For i = 2 To ShtCount
        ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 2), Address:="", SubAddress:= _
                                   "'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name
    Next
    Sheets("目录").Select
    Columns("B:B").AutoFit
    Cells(1, 2) = "目录"
    Set SelectionCell = Worksheets("目录").Range("B1")
    With SelectionCell
        .HorizontalAlignment = xlDistributed
        .VerticalAlignment = xlCenter
        .AddIndent = True
        .Font.Bold = True
        .Interior.ColorIndex = 34
    End With
    Application.StatusBar = False
    Application.ScreenUpdating = True
Tuichu:
End Sub

复制完成后按F5运行即可，如果后期对表名做了修改，只需要重新F5运行刷新下目录即可。

0 0