1、首先在开发工具中打开VBA编辑器
2、在单元格区域当中输入一些内容作为例子
3、在VBA编辑器中插入模块
4、在模块当中输入如下代码，然后运行Private Sub Workbook_Open猾诮沓靥() On Error Resume Next Application.ScreenUpdati荏鱿胫协ng=False '禁止刷屏 Dim XStr, YStr, ZStr XStr="-" ZStr="" For i=1 To Worksheets.Count If Worksheets(i).Name="工作表目录" Then GoTo add: Exit For Next If i > Worksheets.Count Then Sheets.add ActiveSheet.Name="工作表目录" End If Sheets("工作表目录").Move Before:=Sheets(1)add: Sheets("工作表目录").Select With Range("a:b") .Clear .NumberFormatLocal="@" Worksheets(1).Cells(1, 1).Value="编号" Worksheets(1).Cells(1, 2).Value="目录" For i=2 To Worksheets.Count Worksheets(1).Cells(i, 1).Value=i-1 Worksheets(1).Cells(i, 2).Value=Worksheets(i).Name For j=1 To Len(Worksheets(i).Name) YStr=Mid(Worksheets(i).Name, j, 1) If InStr(XStr, YStr) <> 0 Then ZStr="'" Exit For End If Next ActiveSheet.Hyperlinks.add Anchor:=Worksheets(1).Cells(i, 2), Address:="", SubAddress:=ZStr & Worksheets(i).Name & ZStr & "!A1", TextToDisplay:=Worksheets(i).Name Next .HorizontalAlignment=xlCenter '设置目录文字为居中 .VerticalAlignment=xlCenter End With Range("a2").Select '冻结窗格 ActiveWindow.FreezePanes=True ActiveWindow.DisplayGridlines=False '不显示网格线 Application.ScreenUpdating=TrueEnd Sub
5、重新启动工作簿，可以看到工作簿已新加了一个名为“工作表目录”的工作表，且已建立目录，单击该目录中任意表名则进入该表
