Creating Your First Macro

Quick reference

• 00:05 All right, so let's get started with our very first macro.
• 00:08 The key part here that I wanna do is I wanna take this continuity schedule.
• 00:12 This is an accounting listing that shows what our opening balance is, what do we
• 00:16 have for additions and usage, and what do we end up with an ending balance?
• 00:19 And every month, the process that we go through is we select the ending balance,
• 00:23 we copy it, we paste it over the opening balance.
• 00:26 We wipe out the additions and
• 00:28 usage, and then we advance this to the next month up the top here.
• 00:31 So that's a process we do everything month.
• 00:33 So why not automate this to make it a little bit easier?
• 00:37 So to do this, what I'm gonna do is I'm gonna go to the developer tab, and
• 00:40 I'm gonna record a macro to do this for me.
• 00:43 So on the developer tab, what I'm gonna do is I'm gonna click record macro.
• 00:48 And the first thing it says is what's the macro name?
• 00:50 So I'm gonna give it a name like RollForward, and
• 00:53 you can't use spaces here, so just keep that in mind.
• 00:55 If you wanna use spaces, you wanna use an underscore instead.
• 00:58 And it asks me would I like store it in this workbook?
• 01:01 Or would I like to store my macro in a personal workbook or new workbook?
• 01:05 Now I'm gonna choose to store this in This Workbook.
• 01:08 And the difference between these is this.
• 01:10 With Personal Macro Workbook, it creates a workbook that's on my computer that I can
• 01:14 run against any file that's open on my computer.
• 01:17 Which is great, cuz I can use it again in multiple workbooks if I need to.
• 01:21 The problem is if I email that workbook to somebody else,
• 01:23 the macro doesn't go with it.
• 01:25 So now, if I've built this cool macro that allows us to actually work really easily
• 01:28 and I go on vacation, nobody else can use it.
• 01:30 It's very hard to share your personal macro workbook.
• 01:33 So instead, I wanna put the code in this workbook..
• 01:36 Because that way, I can make sure that when I send this workbook to
• 01:39 somebody else, the macro's there so they have access to it.
• 01:42 So there we go.
• 01:43 I give my macro a name.
• 01:44 I could even put in a note here that says, this macro rolls the file forward.
• 01:51 So just something to give me an idea as to what's happening, and we'll say, OK.
• 01:56 Now, at this point when I've said okay, you'll notice there's a little square at
• 02:00 the bottom, and my start recording button is turned to stop recording.
• 02:04 Everything I do at this point will be recorded,
• 02:07 so every mistake I make will be in here.
• 02:09 So you always wanna try and practice this a couple times first.
• 02:13 But for right now, what I'm gonna do is I'm gonna select.
• 02:15 So, E6 through E10, I'm gonna right click on it.
• 02:19 I'm gonna say copy.
• 02:21 I'm gonna go right over to cell B6, and right click, and I'm gonna paste values.
• 02:26 I'm going to select C6 through D10, press Delete.
• 02:31 I'm gonna come over to cell A4 and
• 02:33 I'm gonna type in a little formula called equals.
• 02:36 And let me get my mouse out of the way here, EOMONTH.
• 02:39 I'm gonna take the date from above, comma 1, and close the parenthesis.
• 02:43 What EOMONTH does, it gives me the next month end.
• 02:46 So as I hit Enter, there we are, February 28th.
• 02:49 So we're gonna right-click on this, we'll say Copy.
• 02:52 Right-click, Paste the value, and
• 02:55 then I'm gonna delete March 31st, we'll go back and select this.
• 03:01 I've done everything that I need to do at this point.
• 03:04 So now, I am gonna come back and click, stop recording.
• 03:11 And that's how we go through the process of recording our very first macro.
• 03:15 The big keys that I want you to remember here is where do I store this?
• 03:19 Do I store it in this workbook or do I store it in a personal workbook?
• 03:23 I am big fan of making sure that these macros are generally stored within
• 03:28 this workbook container, because that way when I send the file off to somebody else,
• 03:32 I know they're going to be able to use it.
• 03:34 So that's kind of an important component when I'm actually building these things.
• 03:37 Generally, I don't want to be the person that's actually running it.
• 03:41 The second part that I wanna make sure that you're really,
• 03:44 really aware of on this.
• 03:45 Before you go through, because the Macro Records every single action that you make,
• 03:49 it's really important to go through and practice a couple times to make sure that
• 03:54 you can make this with as minimal problems as possible as you're going through and
• 03:58 build it.
• 03:59 When I first started macros, I would go through it, practice three, four,
• 04:02 five times, because generally, I was trying to do stuff that was a little bit
• 04:05 more complicated, so it would have over 40 steps in there.
• 04:08 And you don't wanna be making a mistake in the middle,
• 04:09 cuz I can sort of compromise things and make them a little ugly.
• 04:12 So practice a couple times first, save the work book,
• 04:16 record your macro, then you're good to go.
• 04:19 And then we'll look at how all this stuff works in a couple of modules here.

Lesson notes are only available for subscribers.