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

 

[Alex Blakenburg]

It’s after midnight here so if it is still looping copy in your latest code and I will have a look tomorrow (your time)

 

[vbaNumpty]

It’s after midnight here so if it is still looping copy in your latest code and I will have a look tomorrow (your time)

Thank you for your assistance I have decided it is better to redo my Power Query and include both sets of Data so that I can connect the slicers instead. I have given up on this endeavor.

 

[Micron]

Access vba is my stronger point so this may not help. Nothing is a keyword that is used to disassociate an object variable with any object in VBA. I would think that extends to Excel vba as well. Can Intersect(Target, Range("I3")) be considered an object in Excel, or is it just a cell address where you're checking for a value? Shouldn't it be

If IsNull(Intersect(Target, Range("I3"))) Then
or since cells can contain leading spaces,

If Intersect(Target, Range("I3")) & vbnullstring = ""

or perhaps
If Trim(Intersect(Target, Range("I3")) & vbnullstring) = ""

In other words, checking for no value in the cell, not whether or not the reference (cell address) is an empty object variable?
Sorry if I'm way off base with my Access vba thinking.

 

[Alex Blakenburg]

Can Intersect(Target, Range("I3")) be considered an object in Excel, or is it just a cell address

The "if not Intersect is nothing" is pretty much standard practice for checking that the Target area is within a predetermined range.
Intersect returns a range object, see Microsoft documentation below.

 

[Alex Blakenburg]

@vbaNumpty
I had initially thought that your issue was with the pivot filtering so I was working on that part of the code in a standard module not as a change event.
I believe the below should work for you.

Note 1: You were right the first time with the intersect, if there is no intersect eg it is nothing you want to exit the sub. Your change to making it "Not" probably contributed to it looping.

Note 2: This only works if the filtering is done at the Page Filter / Report Filter level (xPFile.CurrentPage = x), if you are filtering on a specific column we would need to loop through the items.

Private Sub Worksheet_Change(ByVal Target As Range)

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

    If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub
      
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    Set pt = ActiveSheet.PivotTables("PivotTable3")
    Set xPFile = pt.PivotFields("Customer")
  
    x = ActiveSheet.Range("I3").Text
    xPFile.ClearAllFilters
    On Error Resume Next
    xPFile.CurrentPage = x
   
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    If Err.Number <> 0 Then MsgBox "*** " & x & " *** Is an Invalid Filter Value"
    
End Sub

 

[Micron]

@Alex Blakenburg - thanks. I guess I'd better stick to Access vba questions only.

 

[Alex Blakenburg]

thanks. I guess I'd better stick to Access vba questions only.

Not at all, a different perspective is often useful and always welcome.