Join Dennis Taylor for an in-depth discussion in this video Running a macro from the Quick Access Toolbar button, part of Excel 2013: Macros in Depth.
Some macros might have wider applicability than just in the current worksheet or workbook. In column B, in this worksheet called Mixed Names, we have some formulas that clean up the data in column A. It's done throughout the entire entry of column B, here. And over in column E we've got a different kind of formula, this simply controls the appearance of the data in column D. In both cases, what we'd like to do here is to take the actual results as we see them and use a technique called Paste Values, which in effect throws away the formula and leaves the results.
And no matter which technique we're using here, and you don't have to know much about Excel functions, what we're really trying to do here is to throw away the formulas and keep the values. So, how do we do this manually? Simply select the data - I'll use just a portion of this data here - and we can use copy with Ctrl+C or right-click, copy. And then on Paste Special, you can do it off the paste button if you wish, Paste Values, there's an actual button for it right there, or we could use that. And so, what do we have in these cells? The actual content as we see it. So we'd like to be able to do that at any time.
And fortunately whether we highlight one cell or two cells, if we record the macro, it doesn't make any difference how many we've got highlighted as we record or later when we want to play back the macro, doesn't make any difference how many are highlighted. And let's also suggest that because this is going to be available to other workbooks as well, instead of or in addition to have a keystroke shortcut, we might want to have a new button placed in the Quick Access toolbar. So, let's record the actual macro first. And where we store the macro is critical. And we can do this from the Column E data.
Again, makes no difference what kind of formula's being used. We're going to be turning the formulas into the results. So we've got some data highlighted here. Let's record a macro. We can go to the View tab, the drop arrow under Macros, Record Macro. And maybe we'll just call it something like Paste Values. >> [SOUND] >> Let's forget the keystroke shortcut for now. And let's not store it in this workbook. But in Personal Macro Workbook. If you store a macro here, it will always be available in the future.
No matter which workbook is open. One small caveat there: you must do a normal exit from Excel the next time you do an exit. And I think most of the time that would happen anyway. But, your next exit from Excel will be a normal one. And you will be prompted to save changes you've made to this. If this is the first time you're recording a macro and placing it here, you are in effect creating a personal macro work book. And its actual name is personal.xlsb. So we're about to click OK. Remember, everything we do will be stored in this personal macro workbook and therefore be available indefinitely in the future in any workbook.
We'll click OK, and start the process of simply copying. Ctrl+C is one way to get there. And we can also do this by way of a right-click, we can go to Paste Special this way, and Paste Values, there we are. And a minor point here, let's just press Escape to remove those so-called marquee lines. So we're finished recording. Back to the Macros button, the drop arrow on the View tab. Stop recording. Now, we can make this be assigned to a new button in the Quick Access toolbar simply by going to the Quick Access toolbar, clicking the button on the right, Customize Quick Access Toolbar, and choosing More Commands.
In this dialog box called Excel Options, over on the left side we see Choose Commands From, and we want to click the drop arrow and choose Macros. And we might be seeing macros from different open workbooks, but the one we're looking for is right here. Notice that it's in the file called Personal Paste Values. By clicking add, we are adding this button to the Quick Access toolbar. Now maybe you don't care for that button, so you could choose Modify. There are 181 choices here. I doubt if any one of them immediately suggests paste values, so sometimes it's just a question of maybe picking one of these that you just happen to like the color of.
Here's a butterfly. Why not choose that? The display name that you will see as you slide the mouse over it can be pretty much what you want. So we could just edit this display name here. Take off the first part of it, maybe. You could leave that on, if you wish, to emphasize that it's coming out of Personal or Paste Values, with a space, if you wish. Name it what you want. Click OK, and click OK. And now it's available. Let's try this over here, in Column I we've got a rather complex formula that's replacing some text, but we don't want the formula there anymore, we want these to be turned into their values.
We'll simply click that button, paste values, and what do we have here now? For example, in this cell, simply the result. Now, although you conceivably can do this with any macro, ideally because these buttons are always going to be here anytime you use Excel. Ideally, you only want macros that are stored in Personal Macro workbook in this location. But it's a handy way to get to macros that you use frequently, and you don't need the keystroke shortcut as well, so it's almost as fast as that anyway. So a different technique for storing and being able to get to macros.
- Understanding what macros can do for you
- Running macros
- Creating a Personal Macro Workbook
- Recording macros
- Creating nonrecordable VBA code
- Expanding a macro with statements
- Using absolute and relative references
- Testing a macro in Step mode
- Joining two macros
- Using loop structures
- Streamlining macros