Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The trouble with the macros that we've created so far is that we've been saving them in the workbook that's currently open, and this might be desirable if you have a macro that's specific to one workbook that you're only going to use within the sheets in that workbook and you want to pass it around. That's fine. If you want a macro that's going to be globally available, you need to save it a little differently. Let's go ahead and re-create our titles macro again, and this time we'll save it in our personal workbook. Let's go up to QUARTERLY SALES. This is a title we've already added. Select a range and this time when we record our macro we're going to call this Titles3, set a global shortcut for t, and again this is something that was in a previous workbook, so it's no longer a conflict, and we're going to store it this time instead of This Workbook in a Personal Macro Workbook. This Personal Macro Workbook exists at a higher level outside of the worksheet that you're currently looking at, so it will be available to all future worksheets Say OK. The same process, we can go ahead and perform the actions that we want to record, stop the macro, and it's recorded. Now you can see that if I have a subtitle or another piece of information, let's just create another subtitle right here, and I want to apply the macro that we've previously created, it's going to give us an error saying that that macro doesn't exist where we thought it did, and you'll see that if I right-click to access this item I can't do anything. Remember the Customize dialog box must be open in order for you to access and modify these menus.
Let's go to Tools, Customize and now we can right-click and we'll go down to Assign Macro, and this time we'll assign one from our personal titles. There we go. The PERSONAL.XLS!Titles3, OK. Say OK, Close and now if I select a range ere, My Macros > Title Format, we've just formatted the subtitle using that new macro that's created at a global level and to further illustrate that it's at a global level, let's close this worksheet, no we don't want to save the changes.
We'll open up a new worksheet, so that other worksheet is closed and gone, which means that if the macro was saved only with it, it would be closed and gone. Let's put in title, highlight a range, go up to My Macro and execute this again, and you'll see that it works just as well in a brand-new worksheet as it did in the worksheet we created it in. The only catch is that when you go to exit Microsoft Excel, it's going to ask you, do you want to make changes to the current workbook? No. Do want to make changes to your Personal Macro Workbook? If I say Yes, then that macro will be available to me next time, and if I No, it won't. So obviously we want to click Yes and save our macro work for the next time we open Microsoft Excel.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87391 Viewers
80 Video lessons · 136396 Viewers
59 Video lessons · 55044 Viewers
52 Video lessons · 68912 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.