Why is this code not changing the filter of my pivot table?

[vbaNumpty]

I found this code online and I have tried to use it on my worksheet but the pivot table filter is not changing to match the value in cell

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Sheet6.PivotTables("PivotTable3")
    Set xPFile = xPTable.PivotFields("Customer")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

 

[johnnyL]

Do you have that code in the worksheet that you are monitoring I3 change?

If so, have you stepped through the code to see if it goes through the entire code?

 

[Alex Blakenburg]

The critical lines are the following:-

1) Your new filter value needs to be in I3 on the activesheet and has to have been the last thing changed.

If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub

2) I would change Sheet6 to ActiveSheet or are you trying to change a pivot that is on a different sheet ?
also is PivotTable3 the name of you pivot table on that sheet

Set xPTable = Sheet6.PivotTables("PivotTable3")

3) Just confirm that your pivot table filter field is called exactly Customer

Set xPFile = xPTable.PivotFields("Customer")

 

[vbaNumpty]

Do you have that code in the worksheet that you are monitoring I3 change?

If so, have you stepped through the code to see if it goes through the entire code?

My code is exiting the sub at

If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub

Even though I3 is showing a value when hovered over

 

[vbaNumpty]

The critical lines are the following:-

1) Your new filter value needs to be in I3 on the activesheet and has to have been the last thing changed.

If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub

2) I would change Sheet6 to ActiveSheet or are you trying to change a pivot that is on a different sheet ?
also is PivotTable3 the name of you pivot table on that sheet

Set xPTable = Sheet6.PivotTables("PivotTable3")

3) Just confirm that your pivot table filter field is called exactly Customer

Set xPFile = xPTable.PivotFields("Customer")

When I step through the code it is exiting the sub at :

If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub

Even though the range I3 is showing a value when hovered over.

 

[Alex Blakenburg]

Did it enter the sub when you changed what was in I3. Target will be the cell that was changed.

 

[vbaNumpty]

Did it enter the sub when you changed what was in I3. Target will be the cell that was changed.

So it turns out the logic was backwards, I added Not after the If and now it works, new problem however is that it is creating an infinite loop I believe. New code:

    Dim pt As PivotTable
    Dim xPFile As PivotField
    Dim x As String

    If Not Intersect(Target, Range("I3")) Is Nothing Then Exit Sub
      
    Application.ScreenUpdating = False
   
    Set pt = ActiveSheet.PivotTables("PivotTable3")
    Set xPFile = pt.PivotFields("Customer")
    x = ActiveSheet.Range("I3").Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = x
   
    Application.ScreenUpdating = True

When the code reaches xPFile.ClearAllFilters it gets send back to the top of the code infinitely.

 

[Alex Blakenburg]

Try starting the code with

Application.EnableEvents = False

and ending it setting it back to True

 

[vbaNumpty]

Try starting the code with

Application.EnableEvents = False

and ending it setting it back to True

No luck

 

[Alex Blakenburg]

Did you put the false straight after the if statement ?
And the true as the very last line?