Locked lesson.
About this lesson
Recording a simple Macro in Excel.
Quick reference
Creating your first macro
Recording a new macro.
When to use
When you want to start a new macro from scratch.
Instructions
Recording the Macro
- Go to the Developer tab --> Record Macro
- Provide a name for your macro
- Choose a location to store your macro
- Record the steps
- Go to the Developer tab --> Stop Recording
Choosing where to store the Macro
This Workbook:
- Macro is stored in the solution
- Macro is always available with the workbook
- Allows solution to be portable as macros travel with the workbook
Personal Workbook:
- Macro is stored on your machine in a special workbook
- Allows using the macro no matter which workbook is open
- Macro does not travel with the workbook making sharing difficult
- 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.