The problem has to do with the fact that Range("R2").Copy Range("N2:N15")
attaches the hyperlink in R2
to the entire range of N2:N15
. You can check this by inserting the following code immediately afterwards:
Debug.Print (Range("N2:N15").Hyperlinks.Count)
This returns 1
, meaning the entire range only has one hyperlink (instead of 14, as you might be expecting). If then you check this:
Debug.Print (Range("N2").Hyperlinks(1).Range.Address)
You will see that it returns $N$2:$N$15
. Hence, when you use Range("N2").Hyperlinks.Delete
, you are simply deleting the one hyperlink that is attached to the entire range.
To avoid this, loop through all the cells in your destination range and attach the hyperlink for each cell individually. E.g.
Sub copyHyperlinks()
For Each myCell In Range("N2:N15")
Range("R2").Copy myCell
Next myCell
Debug.Print (Range("N2:N15").Hyperlinks.Count) '= 14
End Sub
Now, Range("N2").Hyperlinks.Delete
will only delete the hyperlink in this particular cell.