When I work with pivottable, I need to refresh them every month for my report. For some months, the whole group appears and sometimes, only some of them. First I will show how to filter with the full name and secondly, to filter by using only the first letters with a wildcard or a star, for instance, instead to put “chocolate”, I put “choco*”.

When I use the macro ?

To filter all names starting with the same word or letters in a pivot table.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How to use the macro ?

It only works if you put the value in “columns” and/or in “rows”.

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Filtering without wildcard:

Sub test()
Dim pvt As PivotTable
Dim pvtField As PivotField
' change the PivotTable1/type names by yours
ActiveSheet.PivotTables("PivotTable1").PivotFields("type").ClearAllFilters
' change the PivotTable1 name by yours
Set pvt = ActiveSheet.PivotTables("PivotTable1")
' change the type name by yours
Set pvtField = pvt.PivotFields("type")
' change chocolate by the value you want to filter
pvtField.PivotFilters.Add xlCaptionEquals, Value1:="chocolate"
End Sub

Filtering with wildcard:

Sub test()
Dim var As Variant
Dim pvt As PivotTable
Dim pvtField As PivotField
' change the letters you want to filter, in this example, it will filter all values beginning with choco
var = "choco*"
' change the PivotTable1/type names by yours
ActiveSheet.PivotTables("PivotTable1").PivotFields("type").ClearAllFilters
' change the PivotTable1 name by yours
Set pvt = ActiveSheet.PivotTables("PivotTable1")
' change the type name by yours
Set pvtField = pvt.PivotFields("type")
pvtField.PivotFilters.Add xlCaptionEquals, Value1:=var
End Sub