Link fields displayed in multiple pivot tables.

blitzer

New Member
Joined
Jan 27, 2009
Messages
9
Hi.

I have three pivot tables that use/display different data that is pulled from a database. The data has a number of discriptions along the left hand side and dates along the top. The data displayed is simply the item (ex. assets) and the corresponding value for the dates selected along the top. Think of a balance sheet from a financial report.

Is there a way that when I select the dates on the first pivot table the other 2 will automatically chose the same dates as well? There are a lot of dates so I am trying to avoid a double 'for each' VBA statement because it takes forever the run on my computer.

thanks in advance.

B
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

blitzer

New Member
Joined
Jan 27, 2009
Messages
9
An extra note.

Its excel 2003.

Also, I tried this, but it is not working.


Dim pi As PivotItem
Dim cell As Range


For Each pi In Sheets("Cap Adq").PivotTables("PivotTable1").PivotFields("MonthEnding").PivotItems
If pi.Visible = True Then
Sheets("BS").PivotTables("PivotTable1").PivotFields("MonthEnding").PivotItems(pi).Visible = True
Else
Sheets("BS").PivotTables("PivotTable1").PivotFields("MonthEnding").PivotItems(pi).Visible = False
End If
Next



I believe the issue stems from the fact that there may be optional dates on one pivot table that are not on the other. These would never be selected by the user, however, I think it may confuse my code.

Thanks in advance again.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Only have about a minute. Look here for an example of synching pivots. A good keyword to seach for here would be "pivottableupdate". Chances are high that someone coding this event will be doing what you're doing.
Code:
Dim cell As Range
Don't use "cell" as a variable name - that's way too close to a keyword.

NOT TESTED - but you should be able to do something like
Code:
For Each pi In Sheets("Cap Adq").PivotTables("PivotTable1").PivotFields("MonthEnding").PivotItems
 Sheets("BS").PivotTables("PivotTable1").PivotFields("MonthEnding").PivotItems(pi.name).Visible = pi.Visible 
Next pi
Not sure off the top of my head whether you would need the pivot item's NAME, CAPTION or VALUE property to properly link them.
Gotta go! HTH,
 

blitzer

New Member
Joined
Jan 27, 2009
Messages
9
The 'cell' reference must have been from a previous attempt at making it work. It is not even used in this code.

I tried your code and it worked. Thanks a lot. I had looked through old posts but was using the tag link rather than sync. I could not find anything.

thanks again.

b
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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