Recording basic Excel macros to automate actions.
Where/when to use the technique
Excel contains a powerful language called VBA that can be used to script actions to happen. Recording macros is the first step in this process, and is very useful for automating repetitive steps.
Expose the Developer tab (if it is not on your ribbon)
- Right click any command on the ribbon and choose “Customize Ribbon”
- Click the box next to Developer tab in the list on the right
- Return to Excel and activate the Developer tab
- Practice the steps you will go through, as the macro recorder will record your mistakes!
- Be aware that the macro will do EXACTLY the same thing EVERY time it runs (this can be modified, but it involves learning to program)
Recording a Continuity Schedule Roll Forward macro
- On the developer tab, click “Record Macro”
- Call the macro “RollForward” and type a capital R in the Shortcut box
- Choose to place the macro in ThisWorkbook
- Select E4:E15 right click, and choose Copy
- Select cell B4, right click, and choose PasteSpecial --> Values
- Select C4:D15 and press Delete
- On the Developer tab click “Stop Recording”
Running the macro
- Enter some data in the data cells and check the balances
- Press Option+Command+R to run the keyboard shortcut
- Press Option+F8 to run the macro from the macro dialog
- Go to the Developer tab and choose to Insert a From Control Button on the worksheet (it will prompt you to connect a macro to it.) You can then click the button to run the macro.
Lesson notes are only available for subscribers.