Excel 单个工作表中如何制作一个类似Word的索引目录

Excel 单个工作表中如何制作一个类似Word的索引目录

打开要创建目录的EXCEL文件--------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

关闭BASIA的界面

在你要插入的电子表格里按ALT+F8

选好你刚才写的模块mulu

点执行

--------OK

本文来源：https://www.wddqw.com/doc/18ad68961937f111f18583d049649b6648d709a1.html