excel 目录生成

刚周经理问我一个问题，他要整理一批资料，想在一个Excel表里面做100多个小的表，这个时候就存在一个问题了，到时候查阅这些小表的内容要找来找出就非常麻烦了。是否可以让这些小表在首页自动生成一个目录，只要点任何一个目录对应的表就可以直接转到这个表上？

查阅了相关资料现将方法分享给大家：打开你要创建，目录的表格按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:=\"\

\"'\" & Sheets(i).Name 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

& \"'!R1C1\

.Font.Bold = True

.Interior.ColorIndex = 34

End With

Application.StatusBar = False

Application.ScreenUpdating = True

Tuichu:

End Sub