Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Copying and pasting techniques
- Working with formulas and functions
- Dealing with formula errors
- Creating lookup tables
- Naming cell ranges
- Formatting data and worksheets
- Finding and replacing data
- Creating SmartArt diagrams
- Creating charts and PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
We're going to record a basic macro that take cells from one worksheet and deposits them at the bottom of this worksheet. Now because this is an Essential Training course we're only giving light coverage to macros. If you're interested in learning about macros in depth, check out some of the really great macro courses in the lynda.com library. But before we begin recording there is some housekeeping that we need to do first. To record macros we need to display the Developer tab on the Ribbon bar. Now on my computer here, you see there is no Developer tab. If you have the Developer tab on your computer you don't have to go through this step.
But I am going to go here to the File tab to go to Backstage View, and down over here let's click Options and we have all the Options categories here on the left and let's click over here where it says Customize Ribbon. Now on the right, we have a list of all the tabs that are visible, Home, Insert, Page Layout and so on, and over here is the Developer tab. So just click there to put a check, click OK, and now you can see there's the Developer tab up there on the Ribbon bar. Click it we have all the Developer tools. And we also need to set security. So click down here on Macro Security and you want to make sure that the second radio button is selected.
It should be selected by default, but I want to make sure that you don't have the first button selected and the idea is that there are nefarious characters writing macro viruses and Excel tries to protect itself against them. If you have at least Disable macros with notification, it means that when Excel sees that there is a workbook that contains macros, it will at least notify you that hey, there are macros here, do you want to enable them or not? If you have that first radio button selected, you won't even get a chance to run the macros. So let's click OK. And since we'll be taking data from another workbook, let's open that workbook now.
So right now we have the recording macro.xlsx, so either press Ctrl+O on your keyboard to open or go to the File tab and Open, and in the Chapter 13 folder of your Exercise Files open up the northeast data file. And you can see the structure is the same as the workbook that we were just looking at. We have Order number, Region, Month, Size and Quantity. Now to switch back and forth between one workbook and another, easiest way I think is to press Ctrl+F6, hold down the Ctrl key and press the F6 key on your keyboard.
That works in just about every single Windows program under the sun, not just Excel. Now for whatever reason you don't like Ctrl+F6, you could always go to go to the View tab and over here Switch Windows, whichever you prefer is fine, and let's go back to the Developer tab. One last thing we need to decide when recording macro is are we recording with relative references or not with relative references? In other words with absolute references. That's what this button lets us decide. Here's the idea. Right now I have Cell B3 selected.
When I record the macro and I hit the down arrow let's say, no surprise I go one cell down into Cell B4. Well, when you are running the macro when you're running the macro what happens if you're starting on Cell C3? When you hit the down arrow what's going to happen? Are you going to go straight down to Cell C4? Well, that's a relative reference, or are you going to go literally to Cell B4, which is an absolute reference, which is what you recorded. So that's what you have to decide is will your navigation be exactly literally the cells that you are recording or will they be relatively going up or going down, left or right? For the purposes of this macro that we are going to record let's turn Relative References on.
Okay, now we are ready to record, so click the Record Macro button and the Record macro dialog box comes up. So let's give it a name. I will call this getData. Now a macro cannot have spaces in the names. You can have either mixed case or you can use underscores. Let's apply a shortcut key. Because we are going to get data, I want to use a shortcut key of Ctrl+Shift+G. So click in this box here and type Shift +G like you are going to type a capital G, and you see it makes it Ctrl+Shift+G. Then you might wonder, hey Bob, why make it Ctrl+Shift+G, why not just plain old Ctrl+G, why make this more difficult? And the answer is that in Excel almost all of the Ctrl this and Ctrl that shortcuts are already used.
Ctrl+G is the command to go to a particular cell. And if we override that here, then Ctrl+G will no longer be to go to a particular cell. But there are just about no Ctrl+ Shift anything in use in Excel, but I'll leave that up to you. That's personal preference. Now we also need to decide where are we storing this macro. Over here it says where to store the macro, and click that down Aarow and you have three choices, Personal Macro Workbook, New Workbook or This Workbook. Well, let me explain. The Personal Macro Workbook is a specific file that Excel keeps, and if you have macros in it, that is if you record macros and put them in the Personal Macro Workbook, they will always be available when you start Excel, because what Excel will do is it will open the Personal macro Workbook when it starts up, but it will open up hidden, so it doesn't get in your way and there is no danger of you messing it up.
Well, if you don't have a Personal Macro Workbook, Excel will create it the very first time you select it. I think it's pretty cool. Now if you choose to store your macro in a new workbook Excel will literally create a brand new workbook and put your macro in there. You would use that option if you intend on distributing your macro to other people. Well, for the purposes of this exercise, let's keep the default of storing the macro in This Workbook, and we will do that because the macro that we are recording is really meant for yhis workbook, so there is no need to store it anywhere else.
Now we could enter a description, and this is just kind of like a yellow sticky note that's attached to it, and we can type-in something like "Gets data from other sheet and deposits them here." Now when we click OK, then recording is live, so click OK. And how do you know recording is live? Up here you see the Stop Recording button. Now real important. When you are done creating the macro, you need to click that Stop Recording button, because if you don't, everything you do including opening and closing files and switching tabs and all that will be recorded, and eventually the recording session will abort, Excel may crash, you won't be very happy, so you just really need to remember to Stop Recording.
Now since we are going to be navigating through this worksheet, I think it's a lot easier and a lot more accurate if we use some handy shortcuts, and that will give you everything you need to know. In general, when I do something like that, I find it's always best to start in the upper-left corner in Cell A1, so press Ctrl+Home. Now the advantage of that is when you playback the macro, it doesn't matter which cell you have selected. You could be selecting Cell Q4300 and you will immediately go back to Cell A1, so you can start selecting the cells if you want.
Now the data that we bring in, we want that to start in Cell A13. Well, right now we wanted to start in Cell A13 because that's the next row, but once we have data from the workbook that we are bringing in, the next time we bring in data, we wanted to start at the first blank row there so it's not always literally A13. So how do we get there? Well, press Ctrl+Down Arrow. The first time you press Ctrl+Down Arrow, it goes to the starting line there. Press Ctrl+Down Arrow again. You see it goes to the end of that section, so you just press Ctrl+Down Arrow twice.
Now just press the Down Arrow by itself. So you are absolutely 100% guaranteed that your cursor is now in the first blank available row. Now we already opened up that other workbook, so let's switch there. Now I am going to press Ctrl+F6. If you prefer, you could go to the View tab and use the Switch Windows command. So I will press Ctrl+F6. Remember this is being recorded by the macro, and like we did before, press Ctrl+Home. So it doesn't matter what cell is selected in this workbook when you open it.
And we will do kind of the same thing. Press Ctrl+Down Arrow once. That gets to the column header. Well we don't want to select the column headers, so just hit the Down Arrow. All right, so we press Ctrl+Home, we then press Ctrl+Down Arrow, then we press the Down Arrow by itself. Now we want to get to the last used row. Now in this case, it's Row 12. In another workbook, it might be a different row. So we want to make sure that the macro gets exactly the data and doesn't leave out any data and doesn't get any blank rows.
So that's why clicking with the mouse really isn't going to work. So press Ctrl+Shift+Down Arrow, so that selects to the end of this row. Now we need to leave these cells selected and also select to the right, so press Ctrl+Shift+Right Arrow. So you are now guaranteed to select all of the data here. Now we need to copy to the Clipboard. I will press Ctrl+C to copy. If you prefer you could go to the Home tab and click the Copy button. Either way will work. Okay, so we have this copied to the Clipboard. We see the marching ants.
Now let's switch back. Again I will press Ctrl+F6, or if you want you could go to the View tab, and Switch Windows. It's your choice. Now our cursor is in the right spot, so all we have do is paste. So I will press Ctrl+V to paste. If you prefer you could go to the Home tab and click Paste. There we go. Now we want to clear off the Clipboard so press the Escape key and let's deselect all of these cells. So just press the Home key. It doesn't really matter which cell you have selected. All right, now we are ready to stop recording.
So go to the Developer tab if you are not already there and click Stop Recording. Okay, great. Well we recorded the macro. How do we know it works? Well, let's bring in data from another region, so right now we have the Central Region, we have the Northeast Region. I am just going to press Ctrl+F6, here is the Northeast data. Let's just close this file, and let's go open another one. So you could press Ctrl+O or go to the File tab and Open. So we have Central, we have Northeast, let's open up Southeast. Double-click it, and you can click anywhere. It doesn't matter.
Maybe go back here to the recording macro. Let's run the macro, press Ctrl+ Shift+G, and boom, there it is. So we have Central, Northeast, Southeast. Let's try it again. Just press Ctrl+F6 and let's close the Southeast data because we already have the data and let's Open and press Ctrl+O or go to the File menu and Open. And let's open up the West data. Okay, so it doesn't matter what cell you have selected, Ctrl+F6, let's switch back here, and again press Ctrl+Shift +G and boom, there's your West data.
And notice there are different numbers of rows for each region. We have Central is a little. Northeast is more. We have a lot more West data than the others. So if you were to try selecting cells by clicking with your mouse you really wouldn't know which cell to select or how many cells to select, but by using those keyboard shortcuts, there is absolutely no chance for error. Well, as long as you do it right. Now one more thing. If we want to save this file with the macro in it, we can't just save with the .xls extension. We have to save this workbook in a macro-enabled format.
So you could either press F12 to Save As or go to the File tab and Save As, and we could keep the name, but for the file type, click that drop-down and up here let's choose Excel Macro-Enabled Workbook, see it's a .xlsm, and then click Save. Now just one more thing. This isn't some exercise I simply dreamed up for this course. This is only a slightly simplified version of a real set of macros I once created for a client that used it every single day. What would happen is in the mornings they would import new data from an order system, using techniques that we covered in the movie on importing and exporting, and they would bring that data into a growing worksheet just like we had here.
So if you completed this exercise, give yourself some congratulations.