I am providing the following code which you can use. You should place the following code on the main filter sheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sValue As Variant
Dim sField As String
sField = "CUSTOMER NAME"
sValue = Target.Value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call doClearFilter("Table1", "PivotTable1", sField)
Call doPivotFilter("Table1", "PivotTable1", sField, sValue)
Call doClearFilter("Locations", "PivotTable8", sField)
Call doPivotFilter("Locations", "PivotTable8", sField, sValue)
Call doClearFilter("Category", "PivotTable10", sField)
Call doPivotFilter("Category", "PivotTable10", sField, sValue)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
Application.CalculateFull
End Sub
Now you should add another module and put the following code in the same.
Code:
Sub doClearFilter(sSheetName As String, sPivotTableName As String, sPivotField As String)
Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sPivotField).ClearAllFilters
End Sub
Sub doPivotFilter(sSheetName As String, sPivotTableName As String, sFieldName As String, sValue As Variant)
Dim vItem As Variant
For Each vItem In Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sFieldName).PivotItems
vItem.Visible = (vItem = sValue)
Next
End Sub
Let me know whether above mention code helped you or not.
Bookmarks