title: List Hyperlinks in Excel
body: <p>I ahve an Excel work book containing a large number of sheets. Each sheet has between 1 and 12 Hyperlinks to different documents on a website. These dicuments are updated from time to time. I would like a macro that lists all the Hyperlinks in a new sheet but also lists the sheet name next to each link. I have the following that lists the Hyperlinks and the cell ref</p>
  
  <pre><code>Sub CopyHyperLinks()  
    Dim rCell As Range 
    Dim ws As Worksheet 
    Dim Lhyper As Long                  
    On Error Resume Next 
    Application.DisplayAlerts = False 
    Sheets("Hypers").Delete 
    On Error Goto 0 
    Application.DisplayAlerts = True 
    Sheets.Add().Name = "Hypers" 
  
    For Each ws In Worksheets
      If ws.Name &lt;&gt; "Hypers" Then 
        For Lhyper = 1 To ws.UsedRange.Hyperlinks.Count  
          ws.Hyperlinks(Lhyper).Range.Copy 
          With Sheets("Hypers").Cells(Rows.Count, 1).End(x1Up)  
            .Offset(1, 0).PasteSpecial
            .Offset(1, 1) = ws.Hyperlinks(Lhyper).Range.Address 
          End
          Application.CutCopyMode = False 
         Next Lhyper 
       End If 
    Next ws 
  End Sub 
  </code></pre>
  
  <p>How can i modify this to show the sheet name instead of the cell ref. 
  is it also possible to then check that these Hyperlinks are valid destinations?</p>