How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4)

welcome to Excel campus my name is John and this is the first video in a series on the personal macro workbook so in this first video we're going to learn exactly what the personal macro workbook is and how to create one and the next video we're going to learn how to customize the ribbon so you can add macro buttons up here from your personal macro workbook and also add those to the quick access toolbar and then in the third video we're going to learn how to create this yes/no message box which will appear before your macro runs so you don't accidentally run the macro and ruin your workbook or your worksheet so this should be everything to get you started with using the personal macro workbook and it will definitely help save you a lot of time when you're using Excel all right so I'm going to show you how to create the personal macro workbook but before I do that I want to explain exactly what it is so I like to think of this as your tool belt for excel or ladies you can think of it as your purse and basically it just holds all of your personal macros or the tools that you'll use to get through the day so these would be tools or macros that help you automate processes that help you save time when working with Excel so the personal macro workbook is just an Excel workbook that is saved or stored on your computer and it contained macros or a bunch of code so here's mine right here we can see in the purse in the VB editor I have this personal dot XLS B file this is the personal macro workbook and it just contains a bunch of modules these are code modules that I've added that contain macros or code that I can run at any time and the personal macro workbook will open every time you open Excel it opens in the background and it's always there always available for you to use so you can run these macros anytime you can also add buttons up here to the ribbon like I have right here in Excel this my macros tab this is a custom tab that I've added that has buttons that run these macros so that's basically what the personal macro workbook is and it just helps you with store your macros and allow you to use them anytime you want okay so let's go ahead and create the personal macro workbook so we're going to use the macro recorder to do this and we'll also need the Developer tab so if you don't see the Developer tab up here in the ribbon you can easily enable it just right-click any tab here and choose customize the ribbon and that will bring up the Excel options window for customized ribbon and then over here on the right side we'll see a list of all the tabs and we want to check the box next to developer to enable the Developer tab so I'm going to hit OK and that will make the Developer tab appear up here in the ribbon the Developer tab just contains some buttons here for the visual basic editor recording macro inserting controls into your workbooks and all kinds of different stuff but we're just going to use this record macro button right here you can also click it down here in the bottom left corner of the application window as well there's a record macro button down there so I'm going to click record macro and that's going to basically bring up this record macro prompt asked us to create a name we're actually going to create a macro here that's going to be called add three sheets something like that and we're going to add a few sheets to the workbook now the important step is you want to choose where it says store macro in you want to choose personal macro workbook this option right here and that's going to actually allow us to or it's going to create a personal macro workbook for us so I'm going to choose that and hit OK and basically in the background it's created a personal macro workbook and it's saving it on our computer so now we can we're actually recording a macro you can tell because it says stop recording up here in the Developer tab and there's also a stop button down here in the bottom left corner so I'm just going to take some actions here to add a few sheets I'm going to insert some sheets into the workbook here just three sheets and that way the macro recorder will actually record those actions so now I'm going to click stop recording to stop recording that and we're going to jump into the visual basic editor to see the code so the visual basic button right here will open the basic editor the keyboard shortcut is alt f11 so I'm going to click that that'll open up the visual basic editor here and we can see our personal workbook our personal macro workbook right here so basically in the project window on the left side you'll see a list of all the workbooks that are currently open on the computer including a few of my add-ins here and then we have the personal macro workbook right here so if we expand this we can actually see the code modules if you double click module 1 that will show us the macro that we just recorded so here's the code to add those sheets basically sheets dot add is a method that will add a new sheet after the active sheet so now we have this macro here in our personal macro workbook and this is basically this workbook is stored on the computer and in the following video I'll show you exactly where it's stored and how to view it as well but basically now we have our personal macro workbook here and every time we open Excel this workbook will be here with our macros that we can run and one really important thing to note is it whenever you're modifying this or making changes to it you always want to save the personal macro workbook and to do that you can just click the Save button right here in VB editor so I'll just click that button that'll save any changes I've made to my personal macro workbook so like I said in the next video we'll take a look at where this map this workbook is stored on your computer and how to view it and then in a subsequent video we'll look at how to add buttons up to the ribbon to run some of what these macros that we use frequently so please leave a comment below if you have any questions about this video if you'd like to learn more about macros and VBA then I have a free three-part video training series that will really help you out in the first video we write our first macro and I also explain the Excel object model if you have ever wondered what the dots between the words mean and what order the code runs in then this video is for you in the second video we automate a common Excel task to create a summary report of all the sheets in the workbook and finally in the third video we build a user form to export the sheets out of the workbook you can learn more and sign up for the free training at Excel campus comm slash macros thanks again for watching and I'll see you soon

Channel: Excel Campus - Jon

Views: 174,583

Rating: 4.9345455 out of 5

Keywords: microsoft excel, excel, macros, vba, personal macro workbook, macro button, excel ribbon, quick access toolbar

Id: rD2-mSbTuL0

Length: 6min 58sec (418 seconds)

Published: Thu Mar 17 2016