Converting Hyperlink formulae to real hyperlink

D

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 ***
 
T

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.
 
G

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.
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top