VBA for filtering a pivot table on multiple value filters - is it possible?

Question

I have a pivot table and I've created a UserForm which should allow the user to select certain filtering options. I would like to filter the rows based on various criteria linked to columns, depending on user input. However, I can't seem to get it to filter the rows on multiple value filters. This doesn't seem to be possible from the front end either, but I'm still hoping that VBA will find a way.

This is how the relevant code (behind the UserForm's OK button) starts off:

'ok button - this is the big one, telling the button what to do with the selected options!

Private Sub CommandButton1_Click()

'targeting our familiar pivot table

Dim pvt As PivotTable
Set pvt = Sheets("Summary").PivotTables("PivotTable1")

'allowing multiple filters to be used at once
pvt.AllowMultipleFilters = True

'and identifying the row that we want to filter - n.b. this would need to change if corporate wanted to use another field as their lowest level row.
Dim pvtFieldBrand As PivotField
Set pvtFieldBrand = pvt.PivotFields("final brand")

'clearing any filters already set
pvtFieldBrand.ClearAllFilters

Then come the filters.
This one works in isolation:

'Filtering depending on whether 1+, 2+ or all 3 audiences favour it

Dim pvtFieldNoAud As PivotField 'then hide the column - at the top of this script, in fact, just in case it's not already hidden
Set pvtFieldNoAud = pvt.PivotFields("Sum of # Audiences")


If OptionButton3 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueIsBetween, DataField:=pvtFieldNoAud, Value1:=1, Value2:=3

End If

If OptionButton4 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueIsBetween, DataField:=pvtFieldNoAud, Value1:=2, Value2:=3

End If

If OptionButton5 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueEquals, DataField:=pvtFieldNoAud, Value1:=3

End If

Then come these filters, which I intended to be multi-select options, but which will only work in complete isolation - not in combination with each other, and not in combination with any other filters:

'Only show if favoured by the selected audiences

Dim pvtFieldWW As PivotField
Set pvtFieldWW = pvt.PivotFields("Sum of WW overindex?")

Dim pvtFieldUU As PivotField
Set pvtFieldUU = pvt.PivotFields("Sum of UU overindex?")

Dim pvtFieldRR As PivotField
Set pvtFieldRR = pvt.PivotFields("Sum of RR overindex?")


If CheckBox1 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=pvtFieldWW, Value1:=0
End If

If CheckBox2 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=pvtFieldUU, Value1:=0
End If

If CheckBox3 = True Then
pvtFieldBrand.PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=pvtFieldRR, Value1:=0
End If

And so on.

I need users to be able to filter this pivot table in up to 6 ways. It could probably be done with Slicers and Report Filters from the front end, but I want to restrict the user's options and the potential for user error, so I was planning on keeping all the relevant options in the UserForm and then locking some functionality so they can't make any other changes to the pivot table. Any ideas much appreciated!

Oh - the error message I get is "Run-time error 1004 - Application-defined or object-defined error". It lands on whichever line in the code represents the second filter called upon. — s.turn
You cannot apply multiple value filters to the same field, even in VBA. The AllowMultipleFilters setting allows you to apply more than one type of filter to the same field, but not more than one of the same type. — Rory
Ah, thanks for confirming. In case anyone reading is interested, I think what I'll try next is to use VBA to filter the source data, then to copy and paste the visible cells only to a new tab, then to create the pivot table from that tab (as suggested here (not my question): experts-exchange.com/questions/24325164/…). Not done any of that before, but I'll record a few macros and see if I can stitch it together. — s.turn
Happy to report that the method in my above comment works an absolute treat! :-) — s.turn

Axel Axel · Accepted Answer · 2018-07-24T15:18:00

There is a simple workaround, which allows you to use multiple filters on a pivottable. Here is a step by step approach that you could use for your approach.

Step 1: Add Helper Columns to the data source with a new heading and any constant value in every row. (You need one helper column per extra filter. If you want to use 3 filters, you need two helper columns)

Step 2: Add the Helpercolumn attributes to your row-fields of the pivot table.

Step 3: Choose the tabular layout where all row attributes are in one row.

Step 4: Now you can apply different filters, one for each attribute in the row-field. This will yield the same result as if you use multiple filters for the "Heading 1".

Step 5: If you now apply this to VBA, the code could look like this:

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")

ActiveSheet.PivotTables("PivotTable1").AllowMultipleFilters = True

With pvt.PivotFields("Heading 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=2
End With
With pvt.PivotFields("Help 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsSmallerThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=11
End With
With pvt.PivotFields("Help 2")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 3"), Value1:=4
End With

VBA for filtering a pivot table on multiple value filters - is it possible?

1 Answers