Excel 自动生成目录

第一步：打开需要制作目录的Excel工作表。

第二步：直接按住组合键 ALT+F11 会弹出一个窗口。

第三步：在弹出窗口中选择菜单：插入-模块，将会弹出一个新窗口。

第四步：在新窗口中，将下面的代码粘贴进去。然后直接按下F5。

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("A:A").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

第五步：关闭所有弹出窗口，回到Excel表格上来。

去看看原来的表就有一个目录了。