Supposing, I have a list of values in column A and each cell contains a different hyperlink, now, I want to copy only the hyperlinks without text to another column E as following screenshot shown. May be there is no direct way to solve this job in Excel, but here, I can introduce a VBA code for dealing with it.
Copy hyperlink from once cell to another with VBA code
Copy hyperlink from once cell to another with VBA codeTo only copy the hyperlink addresses without the text to other cells, the following code can do you a favor, please as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: copy hyperlink only from one cell to another:
Sub CopyHyperlinks()
'Uodateby Extendoffice
Dim xSRg As Range
Dim xDRg As Range
Dim I As Integer
Dim xAddress As String
On Error Resume Next
xAddress = ActiveWindow.RangeSelection.Address
Set xSRg = Application.InputBox("Please select the original range you want to copy hyperlinks:", "KuTools For Excel", xAddress, , , , , 8)
If xSRg Is Nothing Then Exit Sub
Set xDRg = Application.InputBox("Please select the new range you want to paste the hyperlinks only", "KuTools For Excel", , , , , , 8)
If xDRg Is Nothing Then Exit Sub
Set xDRg = xDRg(1)
For I = 1 To xSRg.Count
If xSRg(I) <> "" And xDRg.Offset(I - 1) <> "" Then
If xSRg(I).Hyperlinks.Count = 1 Then
xDRg(I).Hyperlinks.Add xDRg(I), xSRg(I).Hyperlinks(1).Address
End If
End If
Next
End Sub
3. And then press F5 key to run this code, a dialog box will pop out to remind you to select the cells which you want to copy the hyperlinks only, see screenshot:
4. And then click OK, then select the cells that you want to paste the hyperlins only in another dialog box, see screenshot:
5. And the hyperlink addresses have been copied from the original cells to the specified cells as you need, see screenshot:
Note: This code also can help you to copy the hyperlinks from one sheet to another sheet as you want.
