Locked lesson.
About this lesson
Learn to record basic Excel macros to automate your actions (instead of doing them manually).
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Recording Macros.xlsx21.8 KB Recording Macros - Completed.xlsm
30.1 KB
Quick reference
Recording Macros
Recording basic Excel macros to automate actions.
When to use
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.
Instructions
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
- Click OK to return to Excel and activate the Developer tab
Before recording
- 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”
- Give the macro a name (no spaces) and add a capital letter in the Shortcut box (like R) if desired
- Choose to place the macro in ThisWorkbook
- Select a range of cells with data in them, right click, and choose Copy
- Select a cell (the “destination”), right click, and choose PasteSpecial > Values
- On the Developer tab click “Stop Recording”
Running the macro
- Delete the values in the cells you pasted into the “destination” range
- Run the macro in any one of three ways:
- Press CTRL+SHIFT+R (or whatever letter you chose) to run the keyboard shortcut
- Press Alt+F8 to run the macro from the macro dialog
- Go to the Developer tab and choose to Insert a Form 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
- 00:05 We're now gonna look at macros.
- 00:07 And this is exciting stuff because this is where we can actually start to get Excel
- 00:10 to work for us.
- 00:12 Now, I have a job here that I do every month.
- 00:15 Every month, I grab my closing balance, I copy it, I paste this
- 00:18 values over my opening balance, and then I delete this blue section so
- 00:22 that I can put in the new charges and payments to carry these things forward.
- 00:26 I'd really like to find a way to make that easier so that I don't have to right-click
- 00:30 and copy and right-click and paste, and stuff like that.
- 00:32 And this is where a macro is helpful,
- 00:34 because we can script these actions to be replayed upon demand.
- 00:38 But to do it, we're gonna need to expose the ability to actually create macros.
- 00:43 And that's done through the developer tab, which we don't have showing in our tabs.
- 00:47 So to get that we're gonna right click on any tab and go to Customize the Ribbon.
- 00:53 And in here, you can check the box next to Developer.
- 00:56 And now we can say OK.
- 00:58 And you'll have a brand-new tab show up in your Excel ribbon called Developer,
- 01:02 which happens to have the command were looking for, a Record Macro.
- 01:07 There's all kinds of interesting pieces in here, but
- 01:09 this is the one we want right now.
- 01:11 So I'm gonna click on Record Macro and I'm gonna be presented with some options.
- 01:15 First, it's gonna ask me what would I like to call this macro.
- 01:18 I'm gonna go with Rollforward, and you cannot use spaces in this area.
- 01:24 I'd like to trigger it from a keyboard shortcut, Ctrl+Shift+R.
- 01:29 Now if you're gonna start recording macros, I highly recommend that you test
- 01:33 the keyboard shortcut that you wanna use before you actually going on and
- 01:36 record a macro.
- 01:38 Not only is this a great way to learn new keyboard shortcuts, but it'll prevent you
- 01:41 from actually overwriting certain things like, I don't know, Ctrl+P, which prints.
- 01:47 Now if you never use the command, maybe it's not a big deal.
- 01:50 But if you do and you send it to somebody else and
- 01:51 you overwrite their print command, they're not going to be happy with you.
- 01:55 You get a choice of where to store the macro,
- 01:58 in the personal macro workbook or a new workbook or this workbook.
- 02:02 If I want to save this and send it to someone else,
- 02:05 it's important that the code be in this workbook.
- 02:08 For personal macro workbook that would actually be from any workbook that I open
- 02:12 on my computer, we're gonna choose this workbook.
- 02:15 And then we'll say OK.
- 02:17 And at this point there's a couple things happened.
- 02:21 The icon on the developer job changes to a little red box that says Stop Recording.
- 02:26 Down in the bottom on the task bar,
- 02:28 you can also see that we have a little square box.
- 02:30 This is where we could also stop recording the macros as well.
- 02:33 And in this point, we have to be careful because any action that we do
- 02:37 will get written into code and when we trigger the macro, will run again.
- 02:41 So you gotta make sure that you practice this a few times
- 02:44 before you do the recording itself.
- 02:47 So here's what we're gonna do.
- 02:48 I'm gonna grab my closing balance.
- 02:50 I'm gonna press Ctrl+C to copy it.
- 02:53 I'm gonna come over to B6, right click, and I'm gonna paste special values.
- 02:59 And then I'm gonna select my section of data here and press the Delete key.
- 03:05 And that's all I want to do to roll my schedule forward.
- 03:08 So now, I'm gonna go and say Stop Recording.
- 03:13 Now the key thing is, I wanna check and see if it's actually worked.
- 03:16 So let's say that Client 780 bought another $500 and
- 03:22 Client 781 paid $500 off.
- 03:26 At this point, I'd like to know, for next month, if I roll this forward,
- 03:30 will my opening balance for next month become 1,500 and 0.
- 03:34 So let's press, Ctrl+Shift+R.
- 03:38 And just like that, indeed it does.
- 03:40 So I can say now, let's say that this guy here paid this one off, 1,500 and
- 03:45 he bought another 250, Ctrl+Shift+R, and away we go.
- 03:52 It looks pretty nice.
- 03:53 So that's not bad.
- 03:54 We've actually got this scripted so that we can actually replay these commands.
- 03:59 But what if you forget the keyboard shortcut?
- 04:02 Well, you can always go to macros.
- 04:05 Find your macro, and choose options to show what it is.
- 04:10 But there's a better way.
- 04:12 If we cancel that out, we can come back to the developer tab and
- 04:16 go to the Insert box, and the top left control under form controls is a button.
- 04:22 And when we go and left click and
- 04:23 drag, it asks us what would we like to link it to for a macro?
- 04:28 This is the only purpose of a button.
- 04:29 So I'm gonna say roll forward, we'll say OK.
- 04:33 And you'll notice I have little circles around so I can come in here now and
- 04:37 I can change this and I can even go and put this in with a space of roll forward.
- 04:42 And now we'll go and click outside the grid and the buttons will go away.
- 04:44 Now if I need to edit this, I would right click and say Edit Text.
- 04:49 Outside of that I'm gonna click in the grid and now what we'll notice if I go and
- 04:53 say, let's go and add a new charge here with $1,000.
- 04:56 And now I click roll forward, and just like that it works.
- 05:00 And if I go and say, let's knock our payment down for $1,000, for
- 05:03 Closing Balance, it's gonna go from 1,000 to 0.
- 05:06 And boom, there it is.
- 05:07 It's down at 0, and everything is working beautifully with a nice button that my
- 05:11 users will never forget to click.
Lesson notes are only available for subscribers.