How to Record Macro to Automate Excel

Macro are the simple programs using to automate tasks/operations on the excel. They are developed using the programing language "Visual Basic for Applications"(VBA). This  based on Visual Basic. We can record macro on excel and use them later. You have to activate Developer Tab before you record a macro. If you Don't know how, Check my post about

"How to Enable Developer Tab"

Below table will be used to demonstrate the example for record macro.

Brand	Item	List Price	Discount	Selling Price
Nike	Shoe	$111.99		$111.99
Nike	Socks	$21.99
Adidas	Shoe	$58.74
Adidas	Socks	$16.99
Reebok	Shoe	$26.59

Copy above table and paste to cell A1 on blank sheet.

Start Record Macro

1. Select cell E2.
2. Then click on "Record Macro" in Developer Tab.
   
3. After that a new pop up window appears.
4. Now you can change the name of macro and assign a shortcut key to launch this macro on later. For this example we set the macro name as "CopyMacro" and "Ctrl + Shift + A" as the shortcut key.
5. Then click "OK". Now our macro is recording.

Record the Macro

Now we can simulate the actions to be recorded as the macro. Follow below steps to record a simple copy macro.

1. Copy the cell E2 and paste it onto cell E3.
2. Then Click on "Stop Recording" in developer tab to stop recording our macro.
3. Done.

You have recorded the first ever macro recorded by you. Let's check how it's working.

Check the Recorded Macro

1. Delete the cell E3
2. Then enter the "Ctrl + Shift + A" to activate your macro.

Now you can see excel automatically copy cell E2 and paste into cell E3.

Relative References

If you properly inspect the Developer tab, you can see there is an option calling "Use Relative References". What is the use of that? Follow bellow steps and see the difference.

1. Select Use Relative References Option
2. Then Select cell E2.
3. Now click on "Record Macro" in Developer Tab.
4. Then a new pop up window appears.
5. Now you can change the name of macro and assign a shortcut key to launch this macro on later. For this example we set the macro name as "CopyMacroNew" and "Ctrl + Shift + B" as the shortcut key.
6. Then click "OK".
7. After that Copy the cell E2 and paste it onto cell E3.
8. Then Click on "Stop Recording".
9. Then enter the "Ctrl + Shift + B"

See the difference. This time excel copies whichever cell you are selected and paste it into the relative position.

How to Enable Developer Tab in Excel

Excel macro is the way to automate / develop advanced tools in excel. To have this feature you have to enable developer tab first. It is is showing that VBA controls for the excel sheet. You can add, delete  or modify macros through Developer tab. Further the method/process to enable developer tab is based on the excel version you are using.  Therefore please make sure to follow correct method according to your excel version.

Enable Developer Tab in Excel 97-2003

There is no Ribbon on the excel 97-2003 version. That means no tabs in that version. Therefore we can't enable/disable a tab named developer in excel 97-2003. Instead we can activate the Vba editor window using below method.

1. Go to Tools menu
2. Then select Macro.
3. You can find the Visual Basic Editor Command There. Click on it to open VBA Editor.
4. Alternatively You can pre Alt + F11

Enable Developer Tab in Excel 2007

1. Click the "Microsoft Button" on left side upper corner.
2. Then select the "Excel Options"
3. Then click "Popular"
4. After that select the "Show Developer Tab in the Ribbon".

Enable Developer Tab in Excel 2010 & Later

1. Click the "File" Command
2. Then select "Options" button.
3. Then Choose "Customize Ribbon" from the new dialog box.
4. After that select "Developer Tab" check box from the list of "Main Tabs"
5. Finally Click "OK".