Hello! I am trying to modify a macro to allow for 2 ("Owner" and "Period") drop-down list to filter multiple pivot table fields. The drop-down list is on a separate worksheet from the pivot tables.
The code below does exactly what I need for a single drop down list.
Thanks
The code below does exactly what I need for a single drop down list.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Target.Address = Range("N5").Address Then Exit Sub
Dim PT As PivotTable
Dim ptItem As PivotItem
On Error Resume Next
For Each PT In Worksheets("data").PivotTables
With PT.PivotFields("Owner")
If .EnableMultiplePageItems = True Then
.ClearAllFilters
End If
If (Target.Value) = "(All)" Then
.ClearAllFilters
End If
Set ptItem = .PivotItems(Target.Value)
If Not ptItem Is Nothing Then
.CurrentPage = Target.Value
End If
End With
Next
End Sub
Thanks