Linking drop down menus in several pivot tables

[SydneyBridge]

I have several pivot tables in a template worksheet and they hav
dropdown menus to select the files which contain the data. At th
moment I have to go through and change every dropdown menu when I wan
to look at new data. I would like for my other pivot tables to link t
the option I choose in the first pivot table. Is there a way to d
this

 

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