Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Wednesday, 20 February 2013

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.

Friday, 15 February 2013

How to Add Macros in Excel Sheet

Excel is one of the best software created for handling basic database editing. It is user-friendly and widely used all over the globe. Here i will show you how to add Macros in an Excel Sheet.

Steps to Add Macros in Excel Sheet :

Step 1: Open Excel File and go to View Tab
Step 2: Here you will find "Macros". Go to it and then press "Record Macros"


Step 3: Now a dialog box will appear and ask you to write a name and description for Macro. You can also add a shortcut key here. I will suggest you to choose a shortcut key which is not already a shortcut key for excel, because in this case the macro shortcut key will override excel shortcut key. The press OK.

Step 4: Now again go to Macros menu and press "Stop Recording".


 Step 5: Now go to the same menu again and choose "View Macros"

Step 6: Choose the Macro that you have just created and press "Edit".

Step 7: It will open MS Visual Basic to edit the Macro that you have just created.

Step 8: Now you have to just add the Sub code of your new Macro and save it.
Once its saved you can use the shortcut key to run that Macro in your Excel Sheet