XL 2002 Pivot tables: 1 pagefields section -> many tables

A

Arnaud G.

I would like to coordinate several pivot tables I have designed with only 1
PageFields section.
All the Pivot Tables are linked to the same data but display various
statistics in different format (% growth, unit growth, market share....).
They currently all have their own Pagefields section.

Could they be linked to a single one?
 
D

Debra Dalgleish

You can use the following code, adapted from a posting by Robert
Rosenberg. It changes all Pivot Tables if the page is changed on the
first PT. You could revise it to suit your layout. As noted in the code,
place the code on the module for the worksheet which contains the first
Pivot Table.

Dim mvPivotPageValue As Variant
Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable (see above) to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable
Dim ws As Worksheet

Set pvt = ActiveSheet.PivotTables(1)
If LCase(pvt.PivotFields("Year").CurrentPage) _
<> LCase(mvPivotPageValue) Then
For Each ws In ActiveWorkbook.Worksheets
For Each pvt2 In ws.PivotTables
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Year").CurrentPage
pvt2.PageFields("Year").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
Next pvt2
Next ws
End If
End Sub
'====================================
 
Top