Record Macro in Excel

Home/Excel VBA/Record Macro in Excel

The Macro Recorder is a very useful tool available in Microsoft Excel, you can Record Macro in Excel if you want to automate any daily repetitive task, it helps to save the process time and avoid the human errors.If anyone is not knowing about the writing macros in Excel VBA, they can learn Excel VBA macros by recording macros. Macro recorder will store almost every action you are performing after start recording the macro, so it produce more code than required. Plan the task which you want to automate before start recording to avoid this. We can also open the code and delete any statement if it is not necessary, we will see how to edit and modify the code in the following section. Please find the following steps. It will take you through how to record a macro in Excel VBA for the following example.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

Example: In this example I am going to record a macro to ‘change the selected range color’.

In this Topic:

What is a Macro?

A macro is a set of statements to perform some action, you can store this in a file and execute whenever you want to repeat the same kind of action. For example, you have some data in Excel Worksheet1 and you want to copy a range of a data into various locations of different sheets, in this case you can record a macro and save it in a file, you can execute whenever you want to perform the same kind of task for same type of data.

Steps to Record Macro in Excel:

Step 1: Open Excel Workbook.
Step 2: Go to Developer Tab from the Main Ribbon of Excel Window. If Developer Tab is not visible please find at (Link).
Step 3: You can see the Record Macro command button. Please find the below screen shot for the same.

Step 4: Please click on the Record Macro command button. Once you click on the Record Macro command button you can see the following Record Macro dialog box on your screen.

a. Macro Name: Default Macro name will be Macro 1. You can change this name as you want by entering the name in the Macro name field. Here my macro name is ‘Change_Selected_Range_Color’.
b. Shortcut Key: You can assign Shortcut key to the recording macro in the shortcut key field, type any lower case letter or upper case letter that you want to use. You can run a macro by using ‘CTRL + Shift ‘and above mentioned letter entered in the shortcut field. Here I am assigning shortcut key as ‘C’. Now you can run a macro by clicking on ‘CTRL + Shift + C‘.
c. Store macro in: You can store macro in three different locations. Please find the following three location details.

Personal Macro Workbook:

Default file name will be ‘PERSONAL.XLSB’. When we open excel workbook automatically the ‘PERSONAL.XLSB’ workbook also will be open, but it is hidden by default. To see this ‘PERSONAL.XLSB’ workbook we need to unhide it.

Unhide personal macro Workbook:

To Unhide the personal macro workbook please find the following steps.
1. Click on View menu tab from the main ribbon.
2. Go to Window menu and then click on ‘Unhide’. Now you can see Unhide dialog screen as appeared in the following way.

3. Select the ‘PERSONAL.XLSB’ from the Unhide workbook and then click on ok. Now, you can see ‘PERSONAL.XLSB’ workbook.

New Workbook:

You can store the recording macro in the new workbook once you click on OK button in the Record Macro screen, it will automatically open the new workbook and you can see the recorded macro by clicking on Macros command button in the Developer Tab.

This Workbook:

You can store the recording macro in the same workbook where you are writing macro.

d. Description: You can enter the description of the recorded macro in the description field.

Step 5: Click on Ok button after entering all the details of record macro.
Step 6: Go to Home menu tab from the main ribbon. Select a range (A1 to B5) in the excel sheet. Step 7: Go to Font menu from the Home menu tab and then select the yellow color from the Fill Color. For better understand you can see the range (A1 to B5) and it is highlighted in color in the following screen shot.
Note: While doing these operations don’t perform any other actions till you complete the recording macro.

Step 7: Go to Developer tab and then click on ‘Stop Recording’ command button to stop a macro.
Step 8: To view recorded macro click on Macros command button from the developer tab menu.
Now, it will display the Macro dialog box which is shown in the below screen shot.

Step 9: Select the macro which is listed in the macro name and the click on ‘Run’ macro to execute the recorded macro.
Step 10: Click on ‘Step Into’ to start debugging the macro line by line. When we click on ‘Step Into’ it will take you to the code and first line will be highlighted in yellow color.
Note: When we debug the macro line by line, the lines will be highlighted in yellow color.

Step 11: If you want to edit/see the recorded macro code, you can click on the ‘Edit’. It will take you to the code. Now you can see the VBA Editor window with the recorded macro. Please find the following screen shot for the same.

Step 12: If you want to delete the recorded macro click on ‘Delete’ button.

Download File:

Download Record Macro Example File

By |February 17th, 2013|Excel VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. Miiko September 30, 2015 at 9:50 PM - Reply

    I have recorded several macros which will be run on a separate workbook. How can I order the macros or specify which worksheet they apply to?
    Any help is appreciated!
    Thank you!

Leave A Comment

Related pages

application getopenfilenamevba selected rangevba string integersumif formula examplevba saveas methoddashboards for excelexcel macro workbooks openbuilding a dashboard in excelexcel rgbfunctions in excel vbaadding developer tab in excel 2007greater than formula excelclose userform excel vbaspell check shortcut in exceltos codesmacro excel exampleworksheet deleteclustered stacked column chart in excel 2010vba globalfresher hr interview questions and answers pdfhow to stop vba code runningexcel vba msgbox new lineexcel auto adjust row heightvba excel commentexcel sort macrovba ucaseexcel team schedule templateuserforms excelproject charter sample templatesaveas vba excelhow to unhide all hidden rows in exceldml ddlproject management timeline excel templatemacro function in excelcountif function examplenested if vba excelsql vbahow to create combobox in excel 2010excel check duplicatesexcel autofit cellsmod row excelwhat is vlookup in excel definitionvba save excel filemacro programming in excelexcel filter duplicate rowswriting macros excelcan you find duplicates in excelvb codingsvba program examplesexcel macro saveexcel vba hyperlink text to displayexcel formula change cell colorhlookup in excel with examplemerge tabs in excelnot equal vbavba deletevb6 option buttoniferror in excelunprotect excel sheet using vbamicrosoft excel countifexcel project plan ganttvba for excel 2007how to edit the protected excel sheetvlookup for beginnerspivot table excel tutorial 2010excel downloaderhow to fix row in excelvba matchexcel string appendexcel vba usedrangeddl dml dqlexcel macro save as csv