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

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

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

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

Try to duplicate my results in a new blank worksheet:

1. in A2 I entered:
jimmy

2. in J2 I entered:
(E-Mail 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

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

Gary,

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

Regards

D



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