Locked lesson.

Upgrade

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:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Macros.xlsx
10.7 KB Macros - Completed.xlsm
17.7 KB Macros - Extra Practice.xlsx
12.2 KB Macros - Extra Practice Completed.xlsm
24.3 KB

Quick reference

Topic

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
Login to download
  • 00:04 Macros are a great feature that allow you to automate certain tasks inside Excel.
  • 00:09 To work with them effectively you're going to need to show a tab that you may not actually have on your Ribbon right now.
  • 00:15 So in order to find that first what we're going to do is we're just going to go and right click on the Ribbon
  • 00:19 and we're going to say Customize the Ribbon.
  • 00:23 And that point the Excel Options dialog will pop open and what you can see is that this is the place where we can actually go through and
  • 00:31 make modifications, create new Tabs and all that kind of stuff. Well one of the things that's
  • 00:35 apparent down here is that we have this Developer addin.
  • 00:38 You probably won't have Team because this installs with Visual Studio but Developer comes with every
  • 00:43 version of Excel. If we check that box and say OK
  • 00:48 you'll find that you get the Developer tab on your Ribbon. And inside there are some real goodies that we can work with here including Macros.
  • 00:57 Now what we're going do in this particular example is I'm going to go and throw $5000 in new charges for our second customer here.
  • 01:06 So we can see that we have an opening balance we've got $5000 worth of charges and why don't we throw
  • 01:13 $12,000 in payments on our next customer here. Their at $22,000, $12,000
  • 01:17 in payments takes them down. So we've got a new balance thats $10,000.
  • 01:22 What we want to do is we want to take the process of rolling this forward every month so it copies the closing balance over the opening
  • 01:29 balance and clears out this particular area, ready for the new months' charges.
  • 01:34 So the way that we would do this is we would
  • 01:38 select this area here.
  • 01:40 Right click and Copy it.
  • 01:43 We come over here to B4 right click,
  • 01:48 paste the values
  • 01:50 and at that point in time what we would do is then select the blue area and clear it out.
  • 01:56 No problem but before we do that what we should really do is actually click the Record Macro button.
  • 02:03 I'm just going to press escape and stop the marching ants from walking around this stuff. What I'm going to do
  • 02:07 is I'm going to Record Macro.
  • 02:11 And it asks me what would I like for a name? I'm going to call this one RollForward.
  • 02:17 And we have the option of storing it in This Workbook.
  • 02:19 That's where we want to keep it because if I want to email this to anybody else I want them to
  • 02:23 have that ability. And I'm going to hold down my Shift key and press R
  • 02:27 to give it a keyboard shortcut here. Ctrl+Shift+R will run my RollForward Macro.
  • 02:32 When I say OK it goes into recording mode and we can see that because we've got a little white square on the bottom here. We've also got in our
  • 02:39 developer tab a blue square at the top that says Stop Recording.
  • 02:42 So now I'm going to go through and I know these actions because I've
  • 02:45 practiced them very carefully. If you're going to work with this I recommend you practice five or six times to make sure you get it exactly right.
  • 02:52 So I'm going to select my cell.
  • 02:54 Right click and say Copy.
  • 02:57 Come and highlight the cells where I want it to go.
  • 03:00 Right click and Paste Special Values
  • 03:04 and then I'm going to highlight the range of data.
  • 03:07 I'm going to press delete
  • 03:10 and I'm going to go down and press stop recording
  • 03:14 and at this point I have a Macro.
  • 03:17 Now let's see if this actually worked.
  • 03:20 I'm going to put a payment in this one of $10,000.
  • 03:24 That way I can see that this one will go to zero.
  • 03:27 And I'm going to put $14,000 of new charges in my second
  • 03:32 customers line here and that should bring my closing balance up to the exact same amount as the line above.
  • 03:38 So if my Macro works correctly when I run it I should end up seeing $64,000, $64,000,
  • 03:44 0 and then $14,000 so none of these guys will change.It should say $64,000, $64,000, 0.
  • 03:50 So how do we go about running that Macro? Well I can press Control+Shift+R was a
  • 03:54 keyboard shortcut that I made and sure enough $64000, $64,000 and 0.
  • 03:59 Let's try this now. Let's clear this one out.
  • 04:03 And do we have any other options? We do.
  • 04:07 Go to the Insert tab, I can go and insert a Form Control, a button and I can put that right on the worksheet here. And the
  • 04:13 first thing it's going to do, I'm just left clicking and dragging here, the first thing it's going to do when I let go
  • 04:18 is it's going to say which Macro would you like to work with? Well I'm going to click RollForward
  • 04:23 and say OK and now
  • 04:27 when I click that button it will actually roll it forward as well. If I right click my button I can actually also change the text on this one now to
  • 04:36 say Roll Forward
  • 04:40 oops back that up.
  • 04:42 And now I've got a very very handy way
  • 04:46 of automating some of the work that I need to do on a daily basis and I don't have to rely on my users remembering right click, copy,
  • 04:53 paste special, delete this because invariably when you try and replicate those same steps by human hands over and over and over again
  • 05:01 you run into an issue. So this is a cool little technique that you can use to help deal with
  • 05:06 some of those problems and make sure that it's always right every time.

Lesson notes are only available for subscribers.

Gift this course