Syncronizing 2 or more Pivot Tables

vbean

New Member
Joined
Feb 26, 2004
Messages
6
I have several (30+) pivot tables used to generate monthly reports.

I have automated the production of these reports except each month I must go into each pivot table and select the next month.

Is there any way to link or syncronize these pivot tables so I only need to change the month of interest in one place and all of the pivot tables will be updated?

Thanks for your help
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Something like this :-

Code:
    Dim ThisMonth As String
    ThisMonth = ThisWorkbook.Worksheets(1).Range("A1").Value
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.PivotTables(1).PivotFields("month"). _
            CurrentPage = "February"
    Next
 

Ragnar78

Board Regular
Joined
Feb 10, 2004
Messages
210
How would you do it if you specified names?

how can you on the same sheet use a "For each" , with the pivot tables?

my example (it didn't work actually)

------------------------------------------------------------------------
dim nPivot as pivottable

for each npivot in ActiveSheet
npivot.PivotFields(NameOfField).CurrentPage = NameOfValue
next
------------------------------------------------------------------------
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

Using Brian's nice code as a starting point and folding in your new info that some worksheets may have multiple pivot tables. (Your error was not specifying the collection [PivotTables] in your For Each.)

Comments:
  1. No need to activate each sheet. Just make the changes and get on down the road.
  2. I used the active sheet as the "master" instead of keying off the ordinal position of a particular WS which would mean that if you rearrange the Worksheets then you [may] have to update the code.
  3. I assume the starting key as being in B1, not A1 since you indicated that you "wanted to change 1 and the rest follow suit". Note - as this stands you'd need to change one and then run this macro. However you could put this code on either:<ul>
  4. a specific WS that you want to use as a "master" and test for changes to target.address = "$B$1" or
  5. you could put it on the WB_SheetChange, same kind of test + test for existance of PT on WS and then go - this would mean you would have no "master" and a change to page (B1) month in any one PT automatically results in changes to all.
[/list]
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SynchPivots()
    <SPAN style="color:#00007F">Dim</SPAN> ThisMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, pt <SPAN style="color:#00007F">As</SPAN> PivotTable
    
    ThisMonth = ActiveSheet.Range("B1").Value
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> ws.PivotTables
            pt.PivotFields("Month").CurrentPage = ThisMonth
        <SPAN style="color:#00007F">Next</SPAN> pt
    <SPAN style="color:#00007F">Next</SPAN> ws

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

{Edit}I just notice that Ragner was not the OP! VBean, you may or may not need to add the For Each PT loop (it shouldn't hurt anything to leave it in).{EndEdit}
HTH
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
You can do this without code if it helps, using a named range. I tend to use the offset and counta functions to acheive this but there are other ways. Define a named range and in the refers to box the formula would be something like

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
(assumes data starts in cell a1)

Then in the pivot table wizard, when you define the range, use the insert, name, paste menu and paste the name you just created.
This will then select all records including any added and updating the pivot table is just a matter of refreshing the data. (as long as there are no blanks in the data in col a).

Hope this helps
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

GorD - In this case, that wasn't the OP's question. But using a dynamic range as the source for monthly-report PT's is almost always a good tip. And who knows -- maybe he hadn't automated the range-update part of the process...

{Edit}Well, shoot, I just re-read the original post and maybe you are right. I assumed Brian's interpretation of the problem was accurate, but the wording is such that your interpretation is also possible...{EndEdit}
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Greg, your quite right, Iam now reading it that every month we are only interested in the current month and the source data has many months and the user has to turn of one month and leave only the current month showing.
If the 30+pivot tables were off the same source data would updating one not update all - I cant decide without trying it - I know refresh refreshes all.

I am still thinking the dynamic range could still possibly work- somehow. Instead of starting at cell one we would have to formulate a way to start at the first record that matches a date criteria - but then how could we include the field names.

Just thinking out loud.
 

vbean

New Member
Joined
Feb 26, 2004
Messages
6
Thank you all for your suggestions, I have been traveling and have not had a chance to test any of these yet.

Just a few clarifications.
1. I already use dynamic ranges for the source data
2. As you deduced, I am only interested in showing the "Current Month" portion of the data.
3. I am using Excel 2000 SP3 on Win2000

At this point I intend to try the SynchPivots() macro code Greg provided.
 

vbean

New Member
Joined
Feb 26, 2004
Messages
6
I gave the following code a try

Code:
Sub SynchPivots()
    Dim ThisMonth As String, pt As PivotTable
    
    ThisMonth = ActiveSheet.Range("B1").Value
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.PivotFields("Month").CurrentPage = ThisMonth
        Next pt
    Next ws

End Sub
... and it works to set all the pivot tables to the same month, but there are a few issues.
1. The data in the Month column of my data is a date
(i.e. 37922.68) and the format displays it as Feb-04

2. When I use this macro - the Pivot tables are set to 2/1/04, and it is not just a format issue. Some of the data that should be in the pivot tables for Feb-04 is no longer shown. A recalc of the pivot table does not fix it.

3. Also in the pivot table drop down lists the Feb-04 is gone and 2/1/04 appears.

Any Ideas, I am not sure what additional data I should provided to help debug this issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top