一、实现的功能：

一旦增加新的子表，目录页的子表链接可以立即自动更新，同时，子表页自动生成返回目录页的链接。

让多子表的工作簿管理，更加简单、更加高效。

二、VBA代码如下：

主要实现的原理：

VBA的工作簿事件、工作表事件。

1、工作簿事件，实现工作簿模板打开的时候，默认更新一次工作表目录；

2、工作表事件，实现新增工作表的时候，触发自动化更新工作表目录；

Private Sub Workbook_Activate()
    Call 目录自动生成
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Call 目录自动生成
End Sub

Private Sub 目录自动生成()
    Dim sht As Worksheet, xrow As Integer, iMark%, iSht As Worksheet
    Application.ScreenUpdating = False
    
    For Each sht In Worksheets
        If sht.Name = "目录" Then iMark = 1: Exit For
    Next
    If iMark = 0 Then
        Set iSht = Worksheets.Add(before:=Worksheets(1))
        iSht.Name = "目录"
        ActiveWindow.DisplayGridlines = False
    End If
        
    xrow = 2
    Set iSht = Sheets("目录")
    With iSht
        .UsedRange.ClearContents              '建立目前前，先清除目录页的内容
        .Range("A1") = "序号"
        .Range("B1") = "子表名称"
        For Each sht In Worksheets
            If sht.Name <> "目录" And sht.Visible = xlSheetVisible Then          '排除目录页也生成子表目录，同时也不生成隐藏子表的目录
                .Cells(xrow, "A").Value = xrow - 1
                .Hyperlinks.Add Anchor:=.Cells(xrow, "B"), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name
                xrow = xrow + 1
            End If
        Next
    
        With .Range("A1:B5000")
            .NumberFormatLocal = "G/通用格式"
            .Font.Name = "微软雅黑"
        End With
        .Range("A1:A5000").HorizontalAlignment = Excel.xlCenter
        .Range("B:B").Columns.AutoFit
    End With
    
    Call 创建表格目录返回链接
    Application.ScreenUpdating = True
End Sub

Private Sub 创建表格目录返回链接()
    Dim sht As Worksheet, B As Button, Shtn$
    On Error Resume Next
    Shtn = "目录"
    For Each sht In Worksheets
        With sht
            If .Name <> Shtn Then
                Call 删除表格目录(sht)
                Set B = .Buttons.Add(0, 0, 60, 30)  '新建按钮
                With B
                    .Name = Shtn
                    .Characters.Text = "返回目录" '按钮的标题
                    .OnAction = "Thisworkbook.返回目录"        '为按键指定处理子过程
                End With
            End If
        End With
    Next
    Set B = Nothing
End Sub

Private Sub 返回目录()
    ThisWorkbook.Sheets("目录").Activate
End Sub

Sub 删除表格目录(sht As Worksheet)
    Dim n As Integer, i%, j%, objShp As Shape
    Application.DisplayAlerts = False
    For Each objShp In sht.Shapes
        If objShp.Type = 8 Then objShp.Delete
    Next
    Application.DisplayAlerts = True
End Sub