Pivot table

[nc]

I would like to sychronize two pivot table using different
data but having identical rows and columns using VB. The
result of the second pivot table will be based on the
selections of the field ((date) values in one of the
dropdown list of the first table.

Can anyone help?

 

[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
'====================================