|
- Sub Excel各页名()
- Dim XStr, YStr, ZStr
- XStr = " -"
- ZStr = ""
- For i = 1 To Worksheets.Count
- If Worksheets(i).Name = "目录" Then
- Exit For
- End If
- Next
- If i > Worksheets.Count Then
- Sheets.Add
- ActiveSheet.Name = "目录"
- End If
- Sheets("目录").Move Before:=Sheets(1)
- Sheets("目录").Select
- Range("A:B").Clear
- Range("B:B").NumberFormatLocal = "@"
- Worksheets(1).Cells(1, 1).Value = "序号"
- Worksheets(1).Cells(1, 2).Value = "名称"
- For i = 2 To Worksheets.Count
- Worksheets(1).Cells(i, 1).Value = i - 1
- Worksheets(1).Cells(i, 2).Value = Worksheets(i).Name
- For j = 1 To Len(Worksheets(i).Name)
- YStr = Mid(Worksheets(i).Name, j, 1)
- If InStr(XStr, YStr) <> 0 Then
- ZStr = "'"
- Exit For
- End If
- Next
- ActiveSheet.Hyperlinks.Add Anchor:=Worksheets(1).Cells(i, 2), Address:="", SubAddress:=ZStr & Worksheets(i).Name & ZStr & "!A1", TextToDisplay:=Worksheets(i).Name
- Next
- Range("A:A").HorizontalAlignment = xlCenter
- Rows("1:1").HorizontalAlignment = xlCenter
- Cells.Select
- Selection.Columns.AutoFit
- Range("A1").Select
- End Sub
复制代码
|
|