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

definition of vlookup functionvba excel advanced tutorialvba labelexcel vba sql servercontrols for vbatrim functiondynamic charts excelvlookup partial matchexcel vba autofilterrun sub in vbagantt chart template powerpoint free downloadspecialcells vbaexcel insert row vbams excel developer tabremove password protection excel 2013excel vba closeexcel vba on errorgrant syntax in sqlmacro excel insert rowexcel hide columnlearn vba accessvba macro buttonsyntax in excel definitioncapacity planning excel templateexcel enable developer tabvb6 copy filewhy vlookup is usedvba excel moduleupdate sql from excelexcel vba concatenatemicrosoft excel quote templaterows count vbaexcel macro tipsexcel odbc sql queryvba combobox codeword templates agendacutting and pasting worksheetsexcel vba formula with quotesbreak protected sheet password exceloffset example excelproject analysis template excelvba macro tutorial pdfrows in a spreadsheetrelease plan template excelrefresh pivot table vbahow to unlock excelvba set column widthprint macro excelmicrosoft excel vba referenceinterview questions on sql pdfhow to find duplicates in excel sheetvb excel tutorialexcel vba delete sheetexcel sorting macrotick excelhow do you lock a excel spreadsheetswitch statement in vbaworkbook save vbaprotect sheet excel vbaautorun codeinsert macro in excelhow to remove password from protected excel sheetvba commands exceloutlook macro examplesdeclare variables vbadelete duplicate cells exceluserform unload vbavbs file existshow to learn pivot table in excel 2007add developer tab excel 2007vba examples for excelexcel 2010 hyperlinks change to local pathswot analysis template excelvba datediff