Find all Hyperlinks present in a sheet

[vicky]

i need a vba code which finds all hyperlinks present in a sheet and
pastes it in a new sheet.

 

[Gary''s Student]

Sub HyperCopy()
Dim s1 As Worksheet, s2 As Worksheet
Dim h As Hyperlink, s As String
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
For Each h In s1.Hyperlinks
s = h.Parent.Address
s1.Range(s).Copy s2.Range(s)
Next
End Sub

 

[Rick Rothstein]

Just to follow up on Gary''s Student's method... in case you do not want to
copy the hyperlinks from Sheet1 into the same cells addresses over in
Sheet2, but would rather place them adjacent to each other in a column
starting from a specified cell (I used cell C3 in my code), then you can do
it this way...

Sub HyperCopy()
Dim s1 As Worksheet, s2 As Worksheet
Dim h As Hyperlink, s As String, Index As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
For Each h In s1.Hyperlinks
s1.Range(h.Parent.Address).Copy s2.Range("C3").Offset(Index, 0)
Index = Index + 1
Next
End Sub

And, of course, if necessary, you can go across the row (starting in C3)
rather than down the column by changing the Offset property call to
Offset(0,Index) instead.

 

[vicky]

thanks a lot