How to copy cells with HYPERLINK function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, my problem is that I have thousends of cells with HYPERLINK function in
it, but I need to paste them as paste special so the hiperlink is still
enabled but without leaving the function.
 
Here is a possible aproach that you may adapt to your sheet. Let's say we
have a single column of hyperlink formulas, column A. We will use a macro
copy the material in column A to column B, but without formulas.

1. make sure column B is empty (it will receive the converted links)
2. enter the following macro:

Sub cnvrt()
Dim s As String
For Each r In Selection
s = r.Formula
ss = Split(s, Chr(34))
ActiveSheet.Hyperlinks.Add anchor:=r.Offset(0, 1) _
, Address:=ss(1), TextToDisplay:=ss(3)
Next
End Sub

3. in the worksheet, hi-light the cells in col A that contain the hyperlink
formulas
4. run the macro

Non-formula hyperlinks will appear in column B. The stuff in column B is
what you can copy/paste
 
Back
Top