Link fields displayed in multiple pivot tables.

[blitzer]

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

 

[blitzer]

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]

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.

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

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]

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