Say you would like to get a report at the end of the month and each time you get this report, you have to add the self same formatting and formulas to make it look the way it should. As you all know, I love saving time and keystrokes and I hate wasting my time! So performing repetitive steps every month is a no no. wouldn’t it be great if there was a way to record your steps that you create the first time you create your report and then thereafter, you would simply have to click a button to make it happen and complete your report? Well guess what – thanks to Excel 365’s Macro Recorder you can do just that!
When you turn on your Macro Recorder, it will save all of the steps you perform into a macro so that you can later play them back again. your recorded steps are created using VBA code. VBA code is a coding language that has been available for a very long time and it stands for Visual Basic for Applications. A VBA macro is simply a macro with a set of commands that you can reuse. You can create VBA macros with the Macro Recorder unless you prefer writing out all of your code by hand in the VBA Editor.
Before you get started, there are a few things you should be aware of:
It is important to know that when you record a macro, the Macro Recorder captures just about every move you make. So if you make a error in your sequence, (i.e., clicking a button that you did not intend to click, the Macro Recorder will record it. The resolution is to re-record the entire sequence, or modify the VBA code itself. This is why whenever you record something, it’s best to record a process with which you’re highly familiar. The more smoothly you record a sequence, the more efficiently the macro will run when you play it back.
- When you record a macro for performing a set of tasks in a range in Excel, the macro will only run on the cells within the range. So if you added an extra row to the range, the macro will not run the process on the new row, but only the cells within the range.
- If you have planned a long process of tasks to record, plan to have smaller relevant macros instead of having one long macro.
- It is not necessary that only tasks in Excel can be recorded in a macro. Your macro process can extend to other Office applications, and any other applications that support Visual Basic Application (VBA). For example, you can record a macro where you first update a table in Excel and then open Outlook to email the table to an email address.
The Developer tab isn’t displayed by default, but you can add it to the ribbon.
- On the File tab, go to Options > Customize Ribbon.
- Under Customize the Ribbon and under Main Tabs, select the Developer check box.
After you show the tab, the Developer tab will stay visible, unless you clear the check box or have to reinstall a Microsoft Office program.
Follow these steps to record a macro.
- On the Developer tab, in the Code group, click Record Macro or use the shortcut Alt+T+M+R .
- In the Macro name box, enter a name for your macro. Make your name as descriptive as possible so you can find it quickly if you create more than one macro.
Note: The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.
- To assign a keyboard shortcut to run the macro, in the Shortcut key box, type any letter (both uppercase or lowercase will work) that you want to use. It is best to use Ctrl + Shift (uppercase) key combinations, because the macro shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For instance, if you use Ctrl+Z (Undo), you will lose the ability to Undo in that Excel instance.
- In the Store macro in list, select where you want to store the macro.
In general, you’ll save your macro in the This Workbook location, but if you want a macro to be available whenever you use Excel, select Personal Macro Workbook . When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook.
- In the Description box, optionally type a brief description of what the macro does.
Although the description field is optional, it is recommended you enter one. Also, try to enter a meaningful description with any information that may be useful to you or other users who will be running the macro. If you create a lot of macros, the description can help you quickly identify which macro does what, otherwise you might have to guess.
- Click OK to start recording.
- Perform the actions that you want to record.
- On the Developer tab, in the Code group, click Stop Recording ORPress Alt+T+M+R .
Working with recorded macros in Excel
In the Developer tab, click Macros to view macros associated to a workbook. Or press Alt+ F8. This opens the Macro dialog box.
Caution: Macros cannot be undone. Before you run a recorded macro for the first time, make sure that you’ve either saved the workbook where you want to run the macro, or better yet work on a copy of the workbook to prevent unwanted changes. If you run a macro and it doesn’t do what you want, you can close the workbook without saving it.
I hope this is helpful to you!