Excel创建sheet目录

Excel建立目录：

方法1：使用宏表函数

步骤1：在A1单元格建立名字为 目录 的名称管理器
=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())
步骤2：在B1单元格输入以下公式
=IFERROR(HYPERLINK("#’"&目录&"’!A1",RIGHT(目录,LEN(目录)-FIND("]",目录))),"")
特殊说明：
HYPERLINK第一个参数：
“#’”&目录&"’!A1" :其中加粗部分为 英文单引号，否则Excel中的链接无效。

方法2：使用vb脚本

Attribute VB_Name = "模块1"
Sub 目录()
Attribute 目录.VB_ProcData.VB_Invoke_Func = "Q\n14"
On Error GoTo 100
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

For i = 2 To ShtCount
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 1), Address:="", SubAddress:= _
   "'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name
Next
Sheets("目录").Select
Columns("a:a").AutoFit
Cells(1, 1) = "目录"
   
Application.StatusBar = False
Application.ScreenUpdating = True
100:
End Sub