Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting hyperlink to actual web address | Excel Discussion (Misc queries) | |||
How do you keep a hyperlink active after converting to a PDF? | Excel Discussion (Misc queries) | |||
hyperlink between sheets not working after converting to pdf | Excel Discussion (Misc queries) | |||
Converting Text to a hyperlink | Excel Programming | |||
converting hyperlink paths | Excel Discussion (Misc queries) |