Recording Macros

Quick reference

• 00:03 In this video, we're going to look at recording macros.
• 00:07 And this is kind of exciting because this is where we start to
• 00:10 introduce a little bit of automation and making Excel work for
• 00:13 us rather than having to tell it what to do all the time, which is really cool.
• 00:17 The first problem that we have though when we want to get into this is that we don't
• 00:21 have a necessary tab on our ribbon called the developer tab.
• 00:24 So, to fix that we're gonna go to Excel and Preferences.
• 00:29 We're gonna go to View and down near the bottom we're gonna click Developer tab.
• 00:34 And then we'll just close the View Options here.
• 00:36 So we now have a new tab called Developer, which is great, and
• 00:40 on here is a really important button called Record Macro.
• 00:43 So what I'm gonna do is,
• 00:45 I'm gonna record a Macro right now that takes the closing balance, so my $8,900,
• 00:50 it's gonna copy these values, paste them as values over the opening balance.
• 00:55 So we're gonna replace the opening balance.
• 00:57 And then we're gonna clear out the blue cells for entry here.
• 01:01 And this will allow me, every month, to put in the new charges and payments and
• 01:04 carry the balance for it all the time so I can see what's happening with it as I go.
• 01:08 So, here's what we're going to do.
• 01:09 We're going to click record macro,
• 01:11 and we're going to see that we have the option to give our macro a name.
• 01:15 I'm going to call this one roll forward,
• 01:20 because that's what I'm doing is rolling forward this particular spreadsheet.
• 01:23 Now, we have the option to store this in this workbook, or
• 01:27 the personal macro workbook, or a new workbook.
• 01:31 The biggest ones that you'll ever use are this workbook and
• 01:34 the personal macro workbook.
• 01:35 The difference being that macros recorded in this workbook go with the file.
• 01:40 So no matter who you email them to, they'll go along with that file and
• 01:43 can be used by the end user, which is important.
• 01:46 The personal macro workbook is where you would record macros that you want to use
• 01:51 yourself but you may want to use them on several workbooks.
• 01:54 But the challenge here is if you store these files,
• 01:56 or the macro in the personal macro workbook, if you email this workbook to
• 02:00 some one else, they won't be able to use your macro cause they won't have it.
• 02:02 So, we're gonna keep this in this workbook.
• 02:06 You also have the option to put in a shortcut key to run your macro, so I'm
• 02:10 gonna go with a capital R here and you'll notice that means option command shift R.
• 02:15 Okay, that's how you're gonna actually kick the macro off, is one method.
• 02:19 And you can fill out the description for your macro if you want, don't need to.
• 02:22 I'm gonna say okay right now.
• 02:25 So now you should see that the Start Recording button has
• 02:29 turned into a Stop Recording button.
• 02:31 The only other indicator we have is this blue square on the bottom.
• 02:34 And what this means right now is that Excel is recording every action we do,
• 02:38 every Success, every mistake we make.
• 02:40 So it is really important before you guys should go to record a macro you
• 02:44 practice several times because you do not really want to record mistakes here.
• 02:48 So now we are going to carefully replicate the steps that I want to go through.
• 02:51 I am going to select my closing balance.
• 02:53 I am going to right click and I am going to say copy.
• 02:57 I am going to go over to select my opening balance.
• 03:01 Right-click and say Paste Special Values because the items over here are formulas.
• 03:09 So we've locked down those values, and now I'll select my data range and
• 03:13 press Delete, and then I'm gonna stop the recording of the macro.
• 03:18 So that's all it's doing is copying, pasting as values, and
• 03:21 clearing out my data.
• 03:23 Now I'd like to give it a test, so we'll go and
• 03:25 we'll just drop some random values, in to some of these cells.
• 03:30 And we'll go and throw in some credits on the other side as well.
• 03:34 And let's see what we get here.
• 03:37 There we go, perfect.
• 03:40 So now we want to run the macro.
• 03:42 I said my shortcut was Shift+Option+Cmd+R and
• 03:49 you'll notice that it's gone through and it's done exactly that.
• 03:52 There's my 78, 98 that's been copied over.
• 03:55 Let's go in and put some new charges in here.
• 03:57 I'll just put $3,000 in this one and just bring this up.
• 03:59 So we should end up at $10,898.
• 04:00 I'll give you a different way to run the macro
• 04:03 instead of just the keyboard shortcut.
• 04:05 I could click on the macros button over here and it says,
• 04:09 well here's the roll forward macro, would you like to run it?
• 04:12 You'll also notice I have options.
• 04:14 I could change or remember what my keyboard shortcut is,
• 04:18 but I'm gonna just click run.
• 04:21 You'll notice that I now have $10,898 over here.
• 04:23 That's good.
• 04:24 So what if I want to clear one of these things out?
• 04:27 We'll put $1778 in here.
• 04:29 Next month comes along, I want to run this, I could go with Option+F8 and
• 04:34 that'll bring up the Macro dialog box as well.
• 04:37 So those are methods to run our macros and get them working.
• 04:44 There's one more method we could do, and
• 04:46 that is to create a button on our worksheet.
• 04:49 If we click this button, and then we left-click and
• 04:52 drag, the only purpose of one of these buttons is to actually run a macro.
• 04:57 So it will immediately pop up as soon as we let go, and say,
• 04:59 what button would you like to run?
• 05:01 We'll say Roll forward.
• 05:03 Okay, no problem.
• 05:05 And our button shows up on the worksheet.
• 05:07 I can then edit the text in this.
• 05:10 So we'll just go and type in.
• 05:13 Oops, it's kind of a little ugly to work with here.
• 05:15 Roll forward and we'll go all the way to the end and
• 05:20 just delete the remaining text here.
• 05:22 So I've now got a nice little button.
• 05:24 I click outside.
• 05:25 Those little handles go away.
• 05:26 If I right click it, it goes back with these little handles so I can edit it.
• 05:30 If I go and left click those handles go away.
• 05:32 And now you'll notice that when I click that
• 05:35 you can see the macro runs through its steps.
• 05:37 And it's working.
• 05:37 So one final word of note as well.
• 05:40 When you save these files it's really important that you save this as.
• 05:44 An XLSM file.
• 05:46 Not as an XLSX file because XLSX files don't have macros in them.
• 05:53 So you need to change your file format type to use XLSM if you
• 05:58 want to actually preserve your macros, otherwise Excel will just eat them and
• 06:02 they won't be there when you come back next time.

Lesson notes are only available for subscribers.