multiple pivots updated with selection in one table

[Guest]

Also posted in Worksheet Functions, but maybe programming is only way to go...

I have multiple pivots (all taking data link from the first pivot table) on
one sheet. Each sheet will have a "Salesperson" page field. I'd like to be
able to drop down the first table and select the salesperson and have all the
other tables automatically do the same.

Anyone?

 

[Krem]

If I remember correctly, Worksheet_Change event does not work on pag
fields in a pivot table.

The way I would approach this is to add a validation cell with
drop-down box that automatically gets populated with the values fro
the page field, and then use the Worksheet_Change event for that cel
to change the page fields in VBA

 

[Guest]

I am not conversant enough in VBA for that level. Have only done basic level.

If you have anything you can provide, I'd be grateful.

 

[AmyTaylor]

Hi, try adapting the following.
You will need to change the code slightly depending on the number of
pivot tables you have, the sheet name that the pivots are on, and the
pivotfield that you want to make the "change field" - I think you said
it was called salesperson ?
Hope this helps.

Simply put it in a worksheet selectionchange sub routine if you want it
to update automatically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
code here
End Sub



Sub AssimilatePivots()
Dim pvt As PivotTable
Dim pvt2 As PivotTable

Set pvt = Me.PivotTables(1)
Set pvt2 = Sheets("Your sheet name").PivotTables(2)
If LCase(pvt.PivotFields("Salesperson").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Salesperson").CurrentPage
pvt2.PageFields("Salesperson").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
Range("a1").Select
End Sub