Hi All,
I am looking for some assistance writing a working VBA to have 1 pivot filter multiple other pivots on the same page, one filter having multiple field options.


The current logic I am using that works for fields that have only 2 options is:

IsInternalVal = ActiveSheet.PivotTables("Target").PivotFields("IsInternal").CurrentPage
On Error Resume Next
ActiveSheet.PivotTables("DirGoPivot").PivotFields("IsInternal").ClearAllFilters
ActiveSheet.PivotTables("DirGoPivot").PivotFields("IsInternal").CurrentPage = IsInternalVal
If Err.Number <> 0 Then 'if the CurrentPage returns an error
'show all fields
ActiveSheet.PivotTables("DirGoPivot").PivotFields("IsInternal").CurrentPage = "(All)"
Err.Clear 'clear the error for the next set of code
End If

Target being my main pivot table filter.

My pivot field, Month, currently has 4 options – November 2013, December 2013, January 2014 and February 2014.
I’d like to declare a variable for all field options, so when future months are added to the pivot, the VBA won’t need to change.

I’m working on something along the lines of this:

Sub test ()
Dim i As Long
On Error Resume Next
ActiveSheet.PivotTables("Target").PivotFields("Month").PivotItems(1).Visible = True
For i = 2 To Field.PivotItems
If ActiveSheet.PivotTables("Target").PivotFields("Month").PivotItems(i).Month = Value Then _
ActiveSheet.PivotTables("DirGo").PivotFields("Month").PivotItems(i).Visible = True Else _
ActiveSheet.PivotTables("DirGo").PivotFields("Month").PivotItems(i).Visible = False
Next i
If ActiveSheet.PivotTables("Target").PivotFields("Month").PivotItems(1).Month = Value Then _
ActiveSheet.PivotTables("NearbyCategoryPivot").PivotFields("Month").PivotItems(i).Visible = True Else _
ActiveSheet.PivotTables("NearbyCategoryPivot").PivotFields("Month").PivotItems(i).Visible = False
End If
End Sub

Thanks!
Brittni