为excel自动增加目录
打开“开发者工具”选项,运行
Sub getAllWorkSheets()
' 得到所有的sheet页名称,并加上超连接
totalNum = Worksheets.Count
Sheet16.Activate
Range("B:B").Select
Selection.NumberFormatLocal = "@"
' 从2开始就是不带“目录”Sheet页,如果要带,则从1开始
For index_i = 2 To totalNum
sheetName = Worksheets(index_i).Name
Cells(index_i, 2) = sheetName
tar_sheet = "'" & sheetName & "'"
Cells(index_i, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
tar_sheet & "!A1", TextToDisplay:=sheetName
Next index_i
End Sub