Hi Алексей,

You don't need to modify the code, just add the VBA code to the worksheet of the cell you want to reference.
For example, if you want to filter a pivot table named "PivotTable1" in Sheet2 based on the value of cell H6 in Sheet3, please right click the Sheet3 worksheet tab, click View Code from the right-clicking menu, and then add the code to the Sheet3 (Code) window.

Hi Алексей,

Please check if the VBA code in this comment #38754 can help.

Hello ST,
Suppose your target value is in H6 and it changes depending on the value in D18. To filter a pivot table based on this target value. The following VBA code can help. Please give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2022/07/22
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
Dim xBoolean As Boolean
Dim xItsRG As Range
Dim xDDs As Range
Dim xDs As Range
On Error Resume Next

xBoolean = False
Set xRg = Range("h6")

Set xItsRG = Intersect(Target, xRg)
Set xDDs = Intersect(Target.DirectDependents, xRg)
Set xDs = Intersect(Target.Dependents, xRg)
If Not (xItsRG Is Nothing) Then
    xBoolean = True
ElseIf Not (xDDs Is Nothing) Then
    xBoolean = True
ElseIf Not (xDs Is Nothing) Then
    xBoolean = True
End If


If Not xBoolean Then Exit Sub

Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("Pivot Table 1")
Set xPFile = xPTable.PivotFields("Category")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True

End Sub

Hi ST,
I don't quite understand what you mean. In your case, the value of target cell D3 is used to filter the pivot table. The formula in the target cell D3 references the values of cells A15, B15 and C15, which will change according to the values in the reference cells. When any value on A15, B15 and C15 is changed, the pivot table will be automatically filtered if the value in the target cell meets the filter conditions of the pivot table. If the value in the target cell does not meet the pivot table's filtering criteria, the pivot table will be automatically reset to no filtering.

Hi Sagar T,
The code has been updated. Please give it a try. Thanks for your feedback.
Don't forget to change the names of the worksheet, pivot table and the filter in the code. Or you can download the following uploaded workbook for testing.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220805
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
Dim xBoolean As Boolean
Dim xItsRG As Range
Dim xDDs As Range
Dim xDs As Range
On Error Resume Next

xBoolean = False
Set xRg = Range("D3")

Set xItsRG = Intersect(Target, xRg)
Set xDDs = Intersect(Target.DirectDependents, xRg)
Set xDs = Intersect(Target.Dependents, xRg)
If Not (xItsRG Is Nothing) Then
    xBoolean = True
ElseIf Not (xDDs Is Nothing) Then
    xBoolean = True
ElseIf Not (xDs Is Nothing) Then
    xBoolean = True
End If


If Not xBoolean Then Exit Sub
xStr = Format(xRg.Text, "m/d/yyyy")
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet2").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("Date")
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True

End Sub

Hi James,

Yes this is possible, code I used for this is (4 pivots and 2 cell references):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
Dim xFilterStr1, xFilterStr2, yFilterstr1, yfilterstr2 As String
On Error Resume Next
If Intersect(Target, Range("O26:P27")) Is Nothing Then Exit Sub

xFilterStr1 = Range("O26").Value
xFilterStr2 = Range("O27").Value
yFilterstr1 = Range("p26").Value
yfilterstr2 = Range("p27").Value
ActiveSheet.PivotTables("Order_Comp_B2C_Crea").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2B_Crea").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2C_Disp").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2B_Disp").PivotFields("Week Number"). _
ClearAllFilters

If xFilterStr1 = "" And xFilterStr2 = "" And yFilterstr1 = "" And yfilterstr2 = "" Then Exit Sub
ActiveSheet.PivotTables("Order_Comp_B2C_Crea").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2B_Crea").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2C_Disp").PivotFields("Week Number"). _
ActiveSheet.PivotTables("Order_Comp_B2B_Disp").PivotFields("Week Number"). _
EnableMultiplePageItems = True

xCount = ActiveSheet.PivotTables("Order_Comp_B2C_Crea").PivotFields("Week Number").PivotItems.Count
xCount = ActiveSheet.PivotTables("Order_Comp_B2B_Crea").PivotFields("Week Number").PivotItems.Count
yCount = ActiveSheet.PivotTables("Order_Comp_B2C_Disp").PivotFields("Week Number").PivotItems.Count
yCount = ActiveSheet.PivotTables("Order_Comp_B2B_Disp").PivotFields("Week Number").PivotItems.Count

For I = 1 To xCount
If I <> xFilterStr1 And I <> xFilterStr2 Then
ActiveSheet.PivotTables("Order_Comp_B2C_Crea").PivotFields("Week Number").PivotItems(I).Visible = False
ActiveSheet.PivotTables("Order_Comp_B2B_Crea").PivotFields("Week Number").PivotItems(I).Visible = False
Else
ActiveSheet.PivotTables("Order_Comp_B2C_Crea").PivotFields("Week Number").PivotItems(I).Visible = True
ActiveSheet.PivotTables("Order_Comp_B2B_Crea").PivotFields("Week Number").PivotItems(I).Visible = True
End If
Next

For I = 1 To yCount
If I <> yFilterstr1 And I <> yfilterstr2 Then
ActiveSheet.PivotTables("Order_Comp_B2C_Disp").PivotFields("Week Number").PivotItems(I).Visible = False
ActiveSheet.PivotTables("Order_Comp_B2B_Disp").PivotFields("Week Number").PivotItems(I).Visible = False
Else
ActiveSheet.PivotTables("Order_Comp_B2C_Disp").PivotFields("Week Number").PivotItems(I).Visible = True
ActiveSheet.PivotTables("Order_Comp_B2B_Disp").PivotFields("Week Number").PivotItems(I).Visible = True
End If
Next

End Sub

Hi maziaritib4 TIB,
The method is only avaliable for Microsoft Excel. Sorry for the inconvenience.

Hi Justin,
Did you get any error prompt? I need to know more specific about your issue.
Before applying the code, don't forget to modify the "name of the sheet", "name of the pivot table", "name of the filter of pivot table" and the cell you want to filter the pivot table based on (see sceenshot).

Hi Crystal,

Thanks for your help. Issue is the function is not doing anything for some reason. Some clarification:

Pivot name: Order_Comp_B2C
Sheet Name: Calculation Sheet
Filter name: Week Number (I changed this name from what was "Dispatch Week No" in the data file)
Cell to change: O26 and O27 (this should go in range)

In this pivot, I am trying to get the filter changed for the columns, I have nothing in the filter area in the PivotTable Fields menu.

my code is:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("O26")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Calculation Sheet").PivotTables("Order_Comp_B2C")
Set xPFile = xPTable.PivotFields("Week Number")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub

Thanks,

Justin

Hi Justin Teeuw,
I have changed the Pivot name, sheet name, filter name and cell to change to the conditions you mentioned above, and tried the VBA code you provided, it works well in my case. See the following GIF or the attached workbook.
Do you mind creating a new workbook and try the code again?

Hi Crystal,

Attached a screenshot of the pivot, the red box is the filter I would like to change based on the cell value.

Preferably I would like to use a range of cells indicating multiple week numbers.

Thanks,

Justin

Hi Justin Teeuw，
Please try the following VBA code. Hope I can help.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Update by Extendoffice 20220706
    Dim I As Integer
    Dim xFilterStr1, xFilterStr2 As String
    On Error Resume Next
    If Intersect(Target, Range("O26:O27")) Is Nothing Then Exit Sub
    'Application.ScreenUpdating = False
    
    xFilterStr1 = Range("O26").Value
    xFilterStr2 = Range("O27").Value
    ActiveSheet.PivotTables("Order_Comp_B2C").PivotFields("Week Number"). _
        ClearAllFilters
    If xFilterStr1 = "" And xFilterStr2 = "" Then Exit Sub
    ActiveSheet.PivotTables("Order_Comp_B2C").PivotFields("Week Number"). _
        EnableMultiplePageItems = True
    xCount = ActiveSheet.PivotTables("Order_Comp_B2C").PivotFields("Week Number").PivotItems.Count

    For I = 1 To xCount
        If I <> xFilterStr1 And I <> xFilterStr2 Then
            ActiveSheet.PivotTables("Order_Comp_B2C").PivotFields("Week Number").PivotItems(I).Visible = False
        Else
            ActiveSheet.PivotTables("Order_Comp_B2C").PivotFields("Week Number").PivotItems(I).Visible = True
        End If
    Next

    'Application.ScreenUpdating = True
End Sub

Hi Justin,
Sorry I didn't see the screenshot you attached on the page. Maybe there is some error on the page.
If you still need to solve the problem, email me via . Sorry for the inconvenience.

Hi,
Did you get any error prompt? I need to know more specific about your issue, such as your Excel version. And if you don't mind, try to create your data in a new workbook and try agai, or take a screenshot of your data and upload it here.