How to copy hyperlink only from one cell to another in Excel?
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 code
To 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.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
