Menu
I had a column of values in column A, all with 6 digit numbers. In order to hyperlink each of these to a folder containing PDF files with the same 6-digit titles, I inserted the following function into cell A2:
=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)
This worked perfectly, and dragging it down through the thousand or so cells created hyperlinks for each entry.
However, now I want to replace column A1 with these new hyperlinks. I thought I could paste-special the column of hyperlinks to remove the dynamic entries in each cells, but this doesn't seem to be possible. I'm sure there's an easier solution. Am I missing something? Can I make column B exist independent of column A before replacing it? Is there a VBA solution?
502
asked Mar 08 '26 12:03
You can run this quickly in vba procedure:
Sub CreateHyperlinks()
Dim cl As Range
For Each cl In Range("B1:B100").Cells '## Modify as needed
cl.Hyperlinks.Add cl, cl.Value, , , cl.Offset(0,-1).Value
Next
End Sub
If by any chance anyone wants to do it without VBA, you can replace for example
=HYPERLINK(1,2)
with
<html><a href=1>2
but in cases with dynamic formulas it's too complicated because
=HYPERLINK("C:\...Project\" & A2 & ".pdf", A2)
will be
="<html><a href=""C:\...Project\" & A2 & ".pdf"">" & A2
and then it has to be copied as text (for example paste the values in Notepad and copy them again in Notepad) before pasting in Excel.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
