How do I copy hyperlink only (and not text) to another cell? [duplicate]
I have a spreadsheet where column A displays names. There are a few hundred names and each has a different hyperlink (which links to that person's web page). I want to transfer those hyperlinks across to a different column which has different text in and no hyperlinks.
Not every cell in column A has a hyperlink. There are groups of cells merged together, so A2:A7 has one link, A8:A13 the next, A9:10 the next (i.e. number of cells merged is not uniform).
e.g. where A2:A7 reads "Bob" and links to www.bob.com, I want I2:I7, which reads, "Smith," and does not link to anything, to link to www.bob.com. I want to do this repeatedly, copying links from A2:A579 into I2:I579.
The information is copied from a table within a web page, and that is where the hyperlinks come from.
This macro will help
Sub SwapIt()
For i = 2 To 579
If Range("A" & i).Value <> "" And Range("I" & i).Value <> "" Then
Dim newLink As String ' the new link string needs a place to live... just like me!
If Range("A" & i).Hyperlinks.Count = 1 Then
newLink = Range("A" & i).Hyperlinks(1).Address ' Get the old horrible link :)
Range("I" & i).Hyperlinks.Add anchor:=Range("I" & i), Address:=Range("I" & i) ' horrible hack, just to get it to a link
Range("I" & i).Hyperlinks(1).Address = newLink '' replace with the new link... Much better. Like a ray of sunshine
End If
End If
Next i
End Sub
This will move the hyperlink From A2 to I2, then A3 to I3 etc