Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you work with PivotTables you will often find that you want to recreate a particular configuration. You could write down the fields in the order in which they appear, but it's far easier to record a macro that configures a PivotTable for you. So I have a PivotTable that contains sales data for FirmA and FirmB and it's monthly for the years 2009 and 2010 and I also have the Quarter field up here in the Report Filters area and my goal is to recreate this configuration by using a macro. So the first thing we need to do is start recording the macro.
To do that we click the View tab on the Ribbon, click the Macros button's down arrow, and then click Record Macro and I will type in a macro name and I will just call it FirstPosition and now I will click OK and Excel will start recording my actions. The first step to recording a PivotTable macro in Excel is to clear the entire PivotTable and to recreate it from scratch. So in this case, with a cell in the PivotTable selected, I will click the Options tab, click Clear, and then click Clear All.
Now I can recreate the PivotTable from the start. So I will add the Year field to the Row Labels area, Month below that, Company in the Column Labels area, Quarter in the Report Filter area, and then Revenue in the Values area, and I have the same configuration that I had when I started. Now to stop recording, click the View tab on the Ribbon, click the Macros button's down arrow, and click Stop Recording. So now we have a macro that will recreate this PivotTable position.
If you want to view the macro, you can do so by opening it in the Visual Basic Editor. So to do that from the View tab of the Ribbon, click the Macros buttons down arrow, click View Macros and then in the Macro dialog box make sure that the macro you want to see is displayed and highlighted and then click Edit. When you do, the macro opens inside of the Visual Basic Editor. So I will double click to maximize this window within the Visual Basic Editor.
So as you can see there is a lot going on when you create a PivotTable. This first line, Range("B6").Select, isn't actually necessary. It is just that I clicked another cell inside the PivotTable to ensure it was active. It won't hurt the running at all. Then secondly, you can see all of the actions that I took with regards to the PivotTable. So first I cleared the table and then I added the field here to the RowField area in position 1, the field Month to the RowField area in position 2, then I added Company to the ColumnField in position 1 and then Quarter in the PageField area in position 1, and then the Revenue field in the Revenue area, and it uses the SUM summary operation.
So there is a lot going on with PivotTables and anytime that you want to work with PivotTables and use macros, you should never try to create these things by hand. There is just too much going on and it will take you too long and it's way too likely that you will make a mistake. Always record your macro. What I can do, now that I have recorded the macro, is go back into Excel and to do that just go up to the File menu and click Close and Return to Microsoft Excel and then because there is macro inside of this file, we need to save it as a macro-enabled file.
To do that click File and then Save As and then we can keep the same RecordMacro name, but we need to change the tip. So click the Save As type button and then from the list that appears, click Excel Macro Enabled Workbook and then click Save. Now your macro will be ready whenever you want it. The macro recording skills I have taught you in this movie apply to any Excel operation, not just PivotTables. But because PivotTable macros are extremely complicated, it is easiest to record and use them instead of writing them from scratch.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.