Converting Hyperlink formulae to real hyperlink
 

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

Converting Hyperlink formulae to real hyperlink
 

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.

--
Regards,
Tom Ogilvy


"Darin Kramer" wrote:


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

Converting Hyperlink formulae to real hyperlink
 

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.
--
Gary''s Student - gsnu2007


"Darin Kramer" wrote:


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

Converting Hyperlink formulae to real hyperlink
 

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

Converting Hyperlink formulae to real hyperlink
 

Try to duplicate my results in a new blank worksheet:

1. in A2 I entered:
jimmy

2. in J2 I entered:


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

--
Gary''s Student - gsnu200745


"Darin Kramer" wrote:



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

Converting Hyperlink formulae to real hyperlink
 

Gary,

For whatever (unknown!) reason, mine stops on the line Part 1!!!

Regards

D



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