VBA to Enter Specific Text in PivotTable Filter

[jski21]

Good day Mr. Excel Team,

Is there a way through VBA to enter specific text into a PivotTable filter? I'm seeking to enter the search criteria "CR,7008*" into a PivotTable Filter. The search criteria will always be "CR,7008*". I thought recording this would reveal the operation but it doesn't. It simply lists all the excluded entries in the current dataset as False. The CR,7008 data will change from month to month so simply recording the macro and using the resulting code will not work on subsequent months as the code bombs off when it attempts to run through the previous month's excluded entries and can't find them. I then have to manually go into the search filter, check the Select Multiple Items box, and enter CR,7008* in the search criteria field to get the filter to operate properly against the new dataset.

Hope this makes a bit of sense. I'm starting the above code like this:

ActiveSheet.PivotTables("PivotPark").PivotFields("Journall ID").CurrentPage = _
"(All)"
ActiveSheet.PivotTables("PivotPark").PivotFields("Journall ID"). _
EnableMultiplePageItems = True



Thanks in advance for your guidance.


jski

 

[DanteAmor]

Try the two options below to see which one works for you.

Sub FilterEspecifcText_1()
  Dim pTable As PivotTable, pField As PivotField
  Dim xStr As String
  
  xStr = "CR,7008"
  Set pTable = ActiveSheet.PivotTables("PivotPark")
  Set pField = pTable.PivotFields("Journall ID")
 
  pField.ClearAllFilters
  pField.PivotFilters.Add2 xlCaptionContains, Value1:=xStr
End Sub

----- --

Sub FilterEspecifcText_2()
  Dim pTable As PivotTable, pField As PivotField, pItem As PivotItem
  Dim xStr As String, n As Long
  Application.ScreenUpdating = False
  
  xStr = "CR,7008"

  Set pTable = ActiveSheet.PivotTables("PivotPark")
  Set pField = pTable.PivotFields("Journall ID")
 
  pField.ClearAllFilters
  n = 0
  For Each pItem In pField.PivotItems
    If Not LCase(pItem) Like LCase(xStr) & "*" Then
      n = n + 1
      If n < pField.PivotItems.Count Then
        pField.PivotItems(pItem.Value).Visible = False
      Else
        MsgBox "The criteria does not exist."
        pField.ClearAllFilters
      End If
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[jski21]

The second code worked like a charm for me. I received a 1004 Run-time error, "Application-defined or object-defined error" at the final line of the first code; pField.PivotFilters.Add2 xlCaptionContains, Value1:=xStr.

DanteAmor--thank you so much. You've helped make a fairly complex report now run like a charm. I knew there had to be a way to do this, yet my intensive search on the internet proved futile. I really appreciate the help and guidance. This has been very instructive and I can see other possibilities for this down the road. Very useful indeed. Cheers and thanks again!

jski