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 I want I2:I7, which reads, "Smith," and does not link to anything, to link to 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.

8

1 Answer

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

11