Converting Hyperlink formulae to real hyperlink

[Darin Kramer]

Hi there,

I have the following formulae =HYPERLINK("mailto:"&J2,A2)
which creates a working hyperlink.
Problem is I need to move this data to another spreadsheet - If I copy
and paste values it looses the hyperlinking abilities...
question How can I take this hyperlinked formulae and paste it as a
value (ie that does not refer to the other cells), but still works as a
hyperlink...

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

worksheets("sheet2").Formula =
"=Hyperlink(""mailtto:""&Sheet1!J2,Sheet1!A2)"

create a hyperlink with the macro recorder on. Turn it off, then look at
the recorded code. Get the information from your source sheet to supply to
the arguments of the code you recorded.

 

[Gary''s Student]

Assuming that your hyperlink formulas all have the same format (one cell to
carry the email address and another cell to carry the display name), then:

Sub hyper_verter()
'
' gsnu
'
dq = Chr(34)
v = Selection.Formula
v = Replace(v, dq, "")
v = Replace(v, " ", "")
v = Replace(v, "=HYPERLINK(mailto:&", "")
v = Replace(v, ")", "")

st = Split(v, ",")
part1 = Range(st(0)).Value
part2 = Range(st(1)).Value

Sheets("Sheet2").Activate
Set r = Range("B9")
With ActiveSheet
..Hyperlinks.Add Anchor:=r, Address:="mailto:" & part1, TextToDisplay:=part2
End With
End Sub

this macro:

1. gets the formula from the Selected cell
2. gets the address references from the formula
3. gets the email address and display name from the references
4. goes to Sheet2
5. inserts a non-formula hyperlink in cell B9

Put whatever looping structure around this code you desire.

 

[Darin Kramer]

Gary - it gets stuck on the part 1....?

My hyperlink formulae is in column K - I wouldent mind the resutl in
column L...

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gary''s Student]

Try to duplicate my results in a new blank worksheet:

1. in A2 I entered:
jimmy

2. in J2 I entered:
(e-mail address removed)

3. in A5 I entered:
=HYPERLINK("mailto:" & J2,A2)

This link is "hot". Clicking it generates an email

4. I selected A5 and ran the macro and got a hyperlink on Sheet2 which was
also hot