Change 2 Pivot Table Page Fields across all Pivot Tables with 2 drop down selections

[dmarsh]

All,

I need to be able to change 2 pivot table page fields across all pivot tables in a workbook by making selections from 2 drop down selections.

My drop down selections are located on a sheet named "Data Entry".

The first drop down is located in cell C2. I want all Pivot Table Page fields called "Zone" in all pivot tables in the workbook to equal the selection in this cell.

The second drop down is located in cell C5. I want all Pivot Table Page fields called "District" in all pivot tables in the workbook to equal the selection in this cell.

I'm attaching code that works perfectly for one drop down selection. I just don't know how to modify it so that the pivot tables will update based on the second one as well.

Any help would be greatly appreciated. thanks in advance for your help!

---------------

Code that works great for one selection ...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Zone"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("C2").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Beefheart]

Hi,

I was wondering if this question had an answer.

I am stuck trying to get multiple pivots, with 2 fields that are the same to update based on the drop downs in 2 separate cells. I have used the above code to successfully change one field but cannot figure out how to change the second.</SPAN></SPAN>

Thank you in advance.</SPAN></SPAN>