using VBA to filter a pivot table based on a cells value

[JoeyH]

Hello, I have been searching for hours trying to get this code to work. I am trying to get a pivot table to filter based on the value within a specific cell that will change often. When using the code below I get a run-time error '1004': "unable to set the currentPage property of the PivotField class". I have tried a few things I have found online with no luck. Any and all help is greatly appreciated.

--------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

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

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("DATA_SOURCE").PivotTables("TESTING")
Set Field = pt.PivotFields("[Table2].[ID #].[ID #]")

NewCat = Worksheets("DATA_SOURCE").Range("G41").Value

With Field
.ClearAllFilters
.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim pt As PivotTable
  Dim Field As PivotField
  
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target, Range("G41")) Is Nothing Then Exit Sub
  
  Set pt = Worksheets("DATA_SOURCE").PivotTables("TESTING")
  Set Field = pt.PivotFields("[Table2].[ID #].[ID #]")
  
  With Field
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=Target.Value
  End With
End Sub

 

[JoeyH]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim pt As PivotTable
  Dim Field As PivotField
 
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target, Range("G41")) Is Nothing Then Exit Sub
 
  Set pt = Worksheets("DATA_SOURCE").PivotTables("TESTING")
  Set Field = pt.PivotFields("[Table2].[ID #].[ID #]")
 
  With Field
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=Target.Value
  End With
End Sub

Unfortunately that didn't work either. Was given run-time error 1004 "application-defined or object-defined error"

 

[DanteAmor]

Try this. If the value entered in cell G41 does not exist, then all values in the table will be displayed.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim pTable As PivotTable
  Dim pField As PivotField
  Dim pItem As PivotItem
  Dim n As Long
 
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target, Range("G41")) Is Nothing Then Exit Sub
 
  Application.ScreenUpdating = False
 
  Set pTable = Worksheets("DATA_SOURCE").PivotTables("TESTING")
  Set pField = pTable.PivotFields("[Table2].[ID #].[ID #]")
 
  With pField
    .ClearAllFilters
    For Each pItem In .PivotItems
      If LCase(Target.Value) <> LCase(pItem.Value) Then
        n = n + 1
        If n < .PivotItems.Count Then
          pItem.Visible = False
        Else
          .ClearAllFilters
        End If
      End If
    Next
  End With
End Sub

---
If an error occurs, you can point to which line has the error.
I don't think this is a field name: "[Table2].[ID #].[ID #]"
---

 

[JoeyH]

I can't tell you how much I appreciate your time with this! This code seemed to perform without error. however, I have tried a few different ways but it doesn't seem to be filtering anything.

As for not thinking that is a field name I thought the same. However, when recording a macro that is the "field name" given in the macro? I have that field named
" ID # ". Not sure where it gets table 2 from and for or the second ID #.

Thanks again so much for your time

 

[JoeyH]

Continuing to look into this more. Noticed that the "table2" is referencing the table the pivot table is getting its data from.

I also noticed that after applying a filter manually then putting something into E41 it clears all filters I manually put in but doesn't filter the value I placed in E41.

 

[DanteAmor]

Could you post a picture of your sheet?
If I write a6 in cell G41, it automatically does the filtering.
As you can see in the following image, on the left I have the data from the table.

I need to see what you have in the table, what you have in the pivot table, and what you're putting in cell G41.
---

 

[JoeyH]

When I put a value in G41 nothing changes on the pivot table. Was thinking, does it matter that the Pivot table is on a different sheet than the table itself even though it's referencing the table? Thanks

 

[JoeyH]

Could you post a picture of your sheet?
If I write a6 in cell G41, it automatically does the filtering.
As you can see in the following image, on the left I have the data from the table.

View attachment 57530

I need to see what you have in the table, what you have in the pivot table, and what you're putting in cell G41.
---

Hello Dante! Are you still able to assist with this? Thanks again for the time you have already provided.

 

[Alex Blakenburg]

Hello @JoeyH, give this a try.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim pTable As PivotTable
  Dim pField As PivotField
  Dim pItem As PivotItem
  Dim n As Long
  
  Dim Target As Range
  Set Target = Range("G41")
 
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target, Range("G41")) Is Nothing Then Exit Sub
 
  Application.ScreenUpdating = False
 
  Set pTable = Worksheets("DATA_SOURCE").PivotTables("TESTING")
  Set pField = pTable.PivotFields("[Table2].[ID #].[ID #]")
    
   pField.ClearAllFilters

   On Error Resume Next
       pField.CurrentPageName = "[Table2].[ID #].&[" & Range("G41") & "]"
   On Error GoTo 0
    
End Sub