copying hyperlinks

[john sharkey]

Does anyone know how to write the VBA code copy a
hyperlink I tried to record a macro where I select a cell
that has a hyperlink. I choose edit HYPERLINK, COPY
HYPERLINK and then I select cell and paste.

here is the code it generates.


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/29/2004 by JOHN F SHARKEY
'

'
Range("A8").Select
Selection.Copy
Range("E8").Select
ActiveSheet.Paste
End Sub
Any way around this?

Thanks

 

[David McRitchie]

Hi John,
for the simplest in the form of a user defined function:

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

For some additional codings for other links, see
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url