我用网友说的VBA已为EXCEL表格自动生成目录，现在怎样使新建的EXCEL表格自动出现在目录中

并且：怎样使EXCEL工作薄打开时只显示目录，其它工作表为隐藏，点击目录上选中的工作表时不影响打开该选中的工作表，使新建的EXCEL表格保存、关闭后自动出现在目录中，关闭后也自动隐藏,工作表名称变化时，目录上的名称也随之变化。
愿受您指点！
本人QQ：1632229377

假设目录工作表名称为：目录
目录工作表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
将以上代码放到指定位置，即可实现你的功能。

sub test() dim i as integer for i = 30 to 1 step -1 if cells(i, 1) = "" then rows(i).delete next i end sub