为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