EXCEL怎么自动生成现有工作簿的目录，对已隐藏的工作簿不显示在目录中

假设目录工作表名称为：目录
目录工作表A1、B1单元格内容分别为：序号、名称
则对应目录工作表VBA代码为：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
If Selection.Cells.Count = 1 And Selection.Hyperlinks.Count > 0 Then
str = Selection.Value
Worksheets(str).Visible = True
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If
End Sub
整个工作簿（ThisWorkbook）代码：
Private Sub Workbook_Open()
Dim i As Integer
Dim sht As Worksheet
For i = 1 To Sheets.Count
Set sht = Sheets(i)
If sht.Name <> "目录" Then
sht.Visible = xlSheetHidden
End If
Next
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "目录" Then
Dim i, j As Integer
Dim sht As Worksheet
Dim str As String
j = Worksheets.Count
Range("A2:B65536").ClearContents
Range("A2:B65536").Hyperlinks.Delete
For i = 1 To j
str = Sheets(i).Name
If str <> "目录" Then
Cells(i, 1) = i - 1
Cells(i, 2) = str
Sh.Hyperlinks.Add Anchor:=Range("B" & i), Address:="", SubAddress:= _
str & "!A1", TextToDisplay:=str
Sheets(i).Visible = False
End If
Next
Range(Cells(1, 1), Cells(j + 1, 2)).EntireColumn.AutoFit
Cells(1, 2).Select
End If
End Sub
将以上代码放到指定位置，即可实现你的功能。

写代码可以实现