如何在VBA中为我的所有工作簿工作表创建超链接目录？

1 回答

• 0

  有关详细信息，请参阅我的答案List all sheets with link

  Sub CreateLinksToAllSheets()
  Dim sh As Worksheet
  Dim sh2 As Worksheet
  Dim cell As Range
  Dim lRow As Long
  
      'This is the sheet we will add the links to
      Set sh = ActiveWorkbook.Sheets("Sheet1")
      lRow = 1
  
      'Loop each sheet
      For Each sh2 In ActiveWorkbook.Worksheets
  
          'Make sure we are not on the current sheet or a sheet named 
          ' something we don't want to create a link for.
          If ActiveSheet.name <> sh2.name AND sh2.name <> "new customer" AND sh2.name <> "old archive" Then
  
              'Make sure we don't have a single quote in the sheet name.
              strLink = sh2.name
              If InStr(strLink, "'") Then
                  strLink = Replace(strLink, "'", "''")
              End If
  
              'Create a hyperlink to that sheet.
              sh.Hyperlinks.Add Anchor:=sh.Range("A" & lrow), Address:="", SubAddress:="'" & strLink & "'" & "!A1", TextToDisplay:=sh2.name
  
              lRow = lRow + 1
  
          End If
  
      Next sh2
  
  End Sub
  

  回复于 2024-04-19T06:34:02+08:00