How to Record a Macro in Excel (With Detailed Steps)

Excel macros are very useful for performing repetitive activities. If you are a newcomer to the Excel macro then this tutorial is for you. It will guide you with detailed steps to record a macro in Excel. So, without further delay, let’s get started.

Introduction to a Macro in Excel

Macro is basically a VBA code that enables us to run a portion of code each time it is assigned. While recording a macro, Excel monitors our actions and records them in VBA (a language that Excel understands). After the recording has been stopped, saved & executed, Excel just reverts to the VBA code it created and performs the identical actions.

How to Record a Macro in Excel (With Detailed Steps)

Suppose, we have a dataset (B4:C4) in Excel. In this article, we shall demonstrate the step-by-step process to record a time-stamp macro that inserts the current date and time into the active cell (C4). We will use the NOW function at the time of recording the macro for inserting the date & time. Let’s see the steps below to do so.

Step 1: Inserting Developer Tab in Ribbon

The first step for recording a macro in Excel is to insert the Developer tab in the ribbon. If you already have the Developer tab in the ribbon then skip this step.

• First, right-click on any tab in the ribbon area.
• Then, click on Customize the Ribbon.

• In turn, the Excel Options dialog box will appear.
• Therefore, go to Customize Ribbon > Customize the Ribbon > Main Tabs > put a tick mark in the Developer box > OK (see the screenshot).

• Thus, you will find the Developer tab in Ribbon.

Step 2: Recording the Excel Macro

In this step, we will learn how to record a macro in Excel.

• First of all, select cell C4.

• Secondly, go to the Developer tab > Code group > click on Record Macro (see screenshot).

• As a result, the Record Macro dialog box will appear.
• Now, go to Macro name > type any name (TimeStamp) > Shortcut key > Ctrl + Shift + T (you can set any Shortcut key as you wish) > Store macro in > This Workbook > OK.

• Next, to get the current date & time, type the following formula in cell C4:

• After pressing Enter, you will get the current date & time in cell C4.
• Besides, the next cell (C5) will get selected.

• Eventually, to stop the recording: go to the Developer tab > Code group > Stop Recording.

Step 3: Running the Recorded Macro in Excel

Here, we will discuss the process of running the recorded macro.

• To run the macro, first, select an empty cell (C5).

• Now, press Ctrl + Shift + T to execute the macro.
• Consequently, you will find the date & time in cell C5.

Step 4: Examining the Recorded Excel Macro

You can see the VBA code for the macro by following some steps. The steps are below:

• To begin, right-click on the sheet tab (Recording Macro) positioned at the bottom of the Excel worksheet.
• then, select View Code.

• Hence, the VBA code window will open.
• Thereupon, to see the VBA code, double-click on Modules.
• Then, you will find Module1 below Modules.
• Again, double-click on it (Module1).

• Eventually, you will see the following VBA code in the Module1 window (see screenshot):

Sub TimeStamp()
'
' TimeStamp Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C5").Select
End Sub

In the VBA code, the first statement inserts the NOW() formula into the active cell (C4). Moreover, the second statement selects cell C5. It is because I pressed Enter after inserting the formula in cell C4 and so the cell pointer moved to cell C5.

Step 5: Rerecording the Macro in Excel Using Relative References

You can fix the macro in several ways. If you’re an expert in VBA coding, you can also edit the code. However, you will now re-record and to some extent edit the macro that we recorded a while ago. To re-record the macro, we must first delete the previously recorded macro.

• To delete the recorded macro, go to the Developer tab > Code > click on Macros.

• Consequently, the Macro window will pop up.
• Afterward, select the Macro name (TimeStamp).
• Subsequently, click on Delete.

• In turn, the Microsoft Excel message box will appear.
• Click Yes.

• After that, you can start rerecording the macro Using Relative References.
• Before you start your recording again, click the Use Relative References command in the Code group of the Developer tab (see screenshot).
• This control is a toggle. By default, this control is turned off.

• Then, to re-record the macro: select cell C4 > Developer tab > Code group > Record Macro.

• Hence, the Record Macro dialog box will open again.
• However, you can use the previous Macro name or you can change it if you want.
• In this case, we have typed TimeStamp2 in the Macro name box.
• You can also change the Shortcut key for executing the macro or use the previous one.
• At this time we have inserted Ctrl + Shift + D for the Shortcut key.
• The Store macro in box must contain This Workbook.
• Click OK.

• Therefore, go to cell C4 again and time the following formula like before:

• Eventually, press Enter > the next cell (C5) is selected > Developer tab > Code Group > Stop Recording (see screenshot).

• To see the VBA code, right-click on the sheet tab > View code > double-click on Modules > double-click on Module1.
• The following figure shows the new macro, recorded with relative references in effect:

Sub TimeStamp2()
'
' TimeStamp2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

• Finally, you apply the shortcut key (Ctrl + Shift + T) in cell C5.
• As a result, you will see the date & time in cell C5 and the next cell C6 will be selected.

Step 6: Saving the Recorded Macro

In order to save a recorded macro, you must save the Excel file as macro enabled with the ‘.xlsm’ extension. Follow the steps below to see how to do this:

• First, the VBA code window.
• Next, click on the Save button (see screenshot).

• In turn, the Microsoft Excel message box will pop up.
• Now, to save the file as macro-enabled, click on the No option.

• As a result, the Save As dialog box will appear.
• Therefore, click on the Save as type dropdown.
• Consequently, select Excel Macro-Enabled Workbook from the dropdown.
• In the end, click on the Save button.
• Thus, you can save a recorded macro in an Excel workbook.

Step 7: Testing the Recorded Macro

To see if the macro we recorded works properly or not, we can test it. To test the macro we have a new workbook (Testing). This workbook contains a worksheet named Testing Macro. Here, in cell C4 we will apply our recorded macro. The steps to do so are below:

• To test the macro, first of all, you must open the workbook that contains the macro.
• Secondly, select the cell (C4) in which you want to apply the macro.

• While rerecording the macro, we inserted Ctrl + Shift + D as the shortcut key.
• Hence, press it (Ctrl + Shift + D) on the keyboard.
• As a result, you will get the date & time in cell C4 and then the next cell (C5) will be selected.
• That means the macro that we recorded works properly.

Read More: How to Edit Macros in Excel

What Components Are on the Backend of a Recorded Macro in Excel?

To see the components on the backend of a recorded macro, first, you need to open the VBA code window. It’s because the macro is recorded here.

• To open the VBA code window, go to the Developer tab > Code group > Visual Basic.

• In turn, the VBA code window will open.
• Now, to see the code of the macro, double-click on Modules > double-click on Module1.
• In the following picture, we can see all the components of the macro we just recorded.
• Here, the first box (1) is the Menu Bar.
• The second one (2) indicates the Toolbar.
• However, the third box (3) refers to the Project Explorer.
• Finally, the fourth one (4) is the Code Window.

What Gets and Doesn’t Get Recorded by Macro in Excel?

The way the Macro Recorder functions is by following your mouse clicks and keystrokes. If you accidentally click somewhere or input some values, it is also by macro. But sometimes, the macro doesn’t record some accidental clicks and inputs. The list of the things that macro can record and can not record are below.

Things Macro Records:

• Macro records the cells that are selected with the keyboard or mouse.
• Besides, a worksheet’s scrolling gets recorded by macro.
• Moreover, it records the changing of the location of the Excel window.
• Excel macro also records the editing or formatting of the formulas, texts and cells.
• Opening new worksheets or workbooks and saving the workbooks are also recorded by macro.
• Again, macro can record the adding, removing, and shifting of Excel sheets.
• Running different macros also gets recorded.

Things Macro Doesn’t Record:

• Movements of the mouse.
• Switching out the ribbon.
• Things related to the VBA code window.
• Programs that are not Excel-related.

Useful Tips for Recording Macro in Excel

If you follow some techniques or tips then you can save you time and also do the macro related program very efficiently. The tips are below:

• Record macros with the Use Relative References command.
• To activate this command, go to the Developer tab > Code group > click on Use Relative References (see screenshot).

• Utilize keyboard shortcuts to choose ranges.
• Choose your recordings wisely.
• Before executing a macro, create a backup or save the file.
• Keep the recorded macro as simple as possible.

Download Practice Workbook

Download the practice workbook from here.

Conclusion

I hope this tutorial will be helpful for you to record a macro in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

Related Articles

• How to Save VBA Code in Excel
• Excel Macro Shortcut Key
• 25 VBA Macro Example for Enhanced Productivity
• Types of VBA Macros in Excel
• How to Remove Macros from Excel
• How to Save Macros in Excel Permanently
• How to Use Excel VBA to Run Macro When Cell Value Changes
• Excel VBA to Pause and Resume Macro
• Excel Macro Enabled Workbook