Hey, welcome back to the Mr. Excel net cast, I am Bill Jelen. Basics—we start out with massive amounts of data and they say, ‘how we are going to analyze this?’ Well, let us fire up the activity table and see if we can solve this problem.
Hey welcome back to Mr. Excel net cast. I am Bill Jelen. Today the question is sent by Jason from Kentucky. I have to get credit for this answer to Mike Alexander. Mike is my co author on the “Pivot Table Data Crunching” Books. I’m like going to have us to do a 3-day Data Analyst Bootcamp in, the next one in Chicago; April 28, 29 and 30 this year so I pick this one up for Mike.
Jason’s question is. ‘Hey! I have five different Worksheets here. They all have a pivot table, they all; have a date up in the page area of the pivot table. They are all based on the same data set. I would like to be able to change the date on Sheet1 and have it change the page fields and Sheets 2, 3, 4 and 5.’
So we’re going to record a tiny little Macro but then edit the Macros. So we go to View, Macros, Record Macro—I’ll call it TextPT. I’m not going to assign to a shortcut key because this is all are going to happen automatically. So I start on Sheet1 and I choose—let’s just say February from the list and then I go to Sheet2 and choose February and then Sheet three and choose February.
Now, one of the reasons I am recording each one of these is because it gets me the pivot table name for all of these different pivot tables, I’m going to need that. Click okay and then the end Sheet5. I’ll choose February, click okay. And will stop recording. And then I want to go out and look at that Macro, so we’ll go to visual basic and it should be on Macro1.
Okay so, you can see that we’re using a Currentpage Property—the Currentpage Property and basically what I want to have happened is on Sheet 2, I want to change the Currentpage Property to be equal to the value that I just selected on Sheet1 and back here on Sheet1—let’s just take a quick look. It’s in cell in B1, so we’re going to modify this Macro a bit. We don’t’ need to select the Sheet.
So I’ll do Sheets(“Sheet2”).PivotTables(“PivotTable1”).PivotFields(“Date”).Currentpage= and instead of hard quoting at to be February, I’m going to say;
Worksheets(“Sheet1”).Range.(“B1”).Value
Okay so that is basic information. There are few extra things that the Macro recorder recorded here that we don’t really need and basically now that we have that line of code, we see that Sheet3 is also called PivotTable1, Sheet 4, called PivotTable1, Sheet5 called PivotTable1. You may not be that luck. You may find that they all have different names, that is why we gone through recording it that way.
So I’m going to take this line of code, copy it and paste it so I can get to Sheet 3, Sheet4 and Sheet5, okay. I guess you don’t need the first line of code, all right.
Now, I have these lines of code but we’d have to go and run the Macro every time and that is not what I want to do.
I’m going to copy these lines of code; Ctrl-c to copy. I’m going to come back here to Sheet1, the Sheet where the first pivot table is and from the top left through up down, select the Worksheet and from the top right through up down, choose PivotTableUpdate. This is a time, a little bit of code, it’s going to be run every time that we update that first Pivot Table, press Ctrl-v and like this says, every time we change the first Pivot Table, got through and change the other pivot tables.
Let’s give it a try. So we come back here to Tips and Tricks. I’ll go to Sheet1 and I would change to be May, click ok and you don’t see the screen flash or anything but it actually did it. Let me go check the other Pivot Tables, they are updated thanks to that Macros.
So thanks to Mike Alexander for that cool tip like I have a lot of fun doing our 3-day seminar, it’s on Excel and access, think about that if you wan to come to Chicago at the end of April.
Thanks to Jason for sending in this question—very common problem where you have multiple Pivot Tables changed one, and they’ll all change using this method.
I want to thank for stopping by and we’ll see you next time for another net cast from Mr. Excel.
Well thanks for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services