Excel Macro to copy Hyperlink from one cell to another

Excel Macro to copy Hyperlink from one cell to another

You must be here because you have already tried to copy hyperlinks from one cell to another. If its only for one cell then its an easy task. Right click on the cell with hyperlink, click "edit Hyperlink", then copy the hyperlink and the paste it as hyperlink on another cell. But this works gets boring if you have got hundreds of such cells.

That's why we have got macros. I will tell you some simple steps to add one such macro in excel file and then do this work in just one second.

Suppose you excel file is in this format. Links on the left hand side and target cells on right side.

Go to View Tab, and the click on Macros menu and then click on Record Macro. It will open the following Dialog Box. Write a Macro Name, some description and choose a shortcut key. Here i have chosen Ctrl+L .

Then click OK. Now again go to the Macros menu and click on Stop Recording. The click on View Macros in the same menu. Now you will see the following Dialog box. 

 Select the Macro that you have created and click edit. It will open Visual Basic Editor just like shown below.

Now you have to edit the code. Add the following code before "End Sub" .

r = Selection.Rows.Count

For i = 1 To r

ActiveSheet.Hyperlinks.Add Anchor:=Selection.Cells(i, 2), _

Address:=Selection.Cells(i, 1).Hyperlinks(1).Address

Next

Now it will look like the following screenshot. After that click on Save button (the floppy shaped button on the tool bar).

Now close VB Editor and you will be back to your Excel file. Now select the cells from where you want to copy the hyperlinks. 

 The press "Ctrl+L" (as i have chosen it as shortcut key for this macro). Now all links are copied to the cell in front of them.

I hope that this macro will work for you. I will come soon with some other updates.