Jason has five pivot tables based on the same data. All five have a page field holding the month. After changing the month on the first pivot table, he would like the other four pivot tables to also change. Episode 961 shows Mike Alexander's trick for solving this problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a question sent in by Jason from Kentucky and I have to give credit for this answer to Mike Alexander.
Mike is my co-author on the pivot table data crunching books.
I'm going out to do a three-day data analyst boot camp, the next one Chicago April, 28th, 29th, 30th of this year.
So, I picked this one up from 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're all based on the same data set.
I would like to be able to change the date, on sheet 1 and have it, change the page fields and sheets two three four and five and so we're gonna record a tiny little macro.
But then edit the macro.
So, we go to view macros, record macro.
I'll call it, fix pivot table.
I'm not going to assign it to a shortcut key because this is all going to happen automatically.
So, I started out on sheet 1 and I choose let's just say February from the list and then I go to sheet 2 and choose February and then sheet 3 and choose February.
Now, one of the reasons I'm 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 [ ok ] and then in sheet 5, choose February, click [ ok ] and we'll 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 module 1.
Okay! So, you can see that.
We're using a current page property.
Basically, what I want to have happen is on sheet 2 I want to change the current page property.
To be equal to the value that I just selected on sheet 1 and back here on sheet 1.
Let's just take a quick look.
It's in cell B1.
So, we're going to modify this macro a bit.
We don't need to select the sheet.
So, I'll do sheet 2, dot pivot tables, pivot table 1, pivot fields date, current page and instead of hard-coding it to be February.
I'm going to say work sheets, sheet1, dot Range B1 dot value.
Okay, so that's the basic information.
There's a few extra things of the macro recorder, recorded here that we don't really need and basically now that we have that line of code.
We see that it's sheet 3, It's also called Pivot Table 1.
Sheet 4 called pivot Table 1.
Sheet 5 called Pivot table 1.
You may not be that lucky you may find that they all have different names.
That's why we went through and recorded them that way.
So, I'm gonna take this line of code, copy it and paste it.
That way, I can get two sheet, three sheet Four and sheet five okay!
And we also don't need the first line of code, alright!
Now, I have these lines of code but we would have to go and run the macro every time and that's not what I want to do.
I'm going to copy these lines of code, [ ctrl + C ], to copy and we're going to come back here to Sheet 1.
The sheet where the first pivot table is and from the top left drop-down, select worksheet and from the top right drop-down, choose pivot table update.
This is a tiny little bit of code, that's going to be run every time that we update that first pivot table.
Press [ Ctrl + V ], and what this says is every time we change the first pivot table, go through and change the other pivot tables.
Let's give it a try.
So, we come back here to our tips and tricks.
I'll go to sheet 1 and I will change this to be May, click [ ok ] and you don't see the screen flash or anything, but it actually did it.
When we go check the other pivot tables.
They are updating.
Thanks to that macro.
So, thanks to Mike Alexander, for that cool tip Mike and I have a lot of fun doing our three-day seminar.
It's on Excel and Access, think about that if you want to come to Chicago at the end of April.
Thanks to Jason for sending in this question, very common problem.
We have multiple pivot tables, change one page field and they'll all change using this method.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a question sent in by Jason from Kentucky and I have to give credit for this answer to Mike Alexander.
Mike is my co-author on the pivot table data crunching books.
I'm going out to do a three-day data analyst boot camp, the next one Chicago April, 28th, 29th, 30th of this year.
So, I picked this one up from 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're all based on the same data set.
I would like to be able to change the date, on sheet 1 and have it, change the page fields and sheets two three four and five and so we're gonna record a tiny little macro.
But then edit the macro.
So, we go to view macros, record macro.
I'll call it, fix pivot table.
I'm not going to assign it to a shortcut key because this is all going to happen automatically.
So, I started out on sheet 1 and I choose let's just say February from the list and then I go to sheet 2 and choose February and then sheet 3 and choose February.
Now, one of the reasons I'm 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 [ ok ] and then in sheet 5, choose February, click [ ok ] and we'll 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 module 1.
Okay! So, you can see that.
We're using a current page property.
Basically, what I want to have happen is on sheet 2 I want to change the current page property.
To be equal to the value that I just selected on sheet 1 and back here on sheet 1.
Let's just take a quick look.
It's in cell B1.
So, we're going to modify this macro a bit.
We don't need to select the sheet.
So, I'll do sheet 2, dot pivot tables, pivot table 1, pivot fields date, current page and instead of hard-coding it to be February.
I'm going to say work sheets, sheet1, dot Range B1 dot value.
Okay, so that's the basic information.
There's a few extra things of the macro recorder, recorded here that we don't really need and basically now that we have that line of code.
We see that it's sheet 3, It's also called Pivot Table 1.
Sheet 4 called pivot Table 1.
Sheet 5 called Pivot table 1.
You may not be that lucky you may find that they all have different names.
That's why we went through and recorded them that way.
So, I'm gonna take this line of code, copy it and paste it.
That way, I can get two sheet, three sheet Four and sheet five okay!
And we also don't need the first line of code, alright!
Now, I have these lines of code but we would have to go and run the macro every time and that's not what I want to do.
I'm going to copy these lines of code, [ ctrl + C ], to copy and we're going to come back here to Sheet 1.
The sheet where the first pivot table is and from the top left drop-down, select worksheet and from the top right drop-down, choose pivot table update.
This is a tiny little bit of code, that's going to be run every time that we update that first pivot table.
Press [ Ctrl + V ], and what this says is every time we change the first pivot table, go through and change the other pivot tables.
Let's give it a try.
So, we come back here to our tips and tricks.
I'll go to sheet 1 and I will change this to be May, click [ ok ] and you don't see the screen flash or anything, but it actually did it.
When we go check the other pivot tables.
They are updating.
Thanks to that macro.
So, thanks to Mike Alexander, for that cool tip Mike and I have a lot of fun doing our three-day seminar.
It's on Excel and Access, think about that if you want to come to Chicago at the end of April.
Thanks to Jason for sending in this question, very common problem.
We have multiple pivot tables, change one page field and they'll all change using this method.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
