VBA to filter pivot table

[KimC2504]

Hello,

I am trying to set up VBA to filter my pivot table column by Q1 and the total column by >50 there are several other columns which remain unfiltered.

I have used the following code

ActiveSheet.Range("$B$21").AutoFilter Field:=1, Criteria1:="Q1"
    ActiveSheet.Range("$N$21").AutoFilter Field:=13, Criteria1:=">50", _
        Operator:=xlAnd

I get error messages like "Show all Data method of worksheet code failed" or "Auto Filter method of range class failed"

Any suggestions what I should do?

 

[Yongle]

Your code is written for standard ranges and is not applicable to Pivot Tables - see this excellent VBA Guide To Excel Pivot Tables

 

[KimC2504]

I have tried this method as well and get error messages "Unable to get the PivotFields property of the PivotTable class"

Code I used:

Sub ReportFiltering_Single()
'PURPOSE: Filter on a single item with the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("Review").PivotFields("Qtr")

'Clear Out Any Previous Filtering
  'pf.ClearAllFilters

'Filter on 2014 items
  pf.CurrentPage = "Q1"

End Sub

 

[Yongle]

The code works fine
- is your pivot table named Review ?
- is your pivot table set up with Qtr as one of the filters ?

Code used (identical to yours):

Sub ReportFiltering_Single()
    Dim pf As PivotField
    Set pf = ActiveSheet.PivotTables("Review").PivotFields("Qtr")
    pf.ClearAllFilters
    pf.CurrentPage = "Q1"
End Sub

Data with Before & after pivot tables:

Behind the scenes

Make use of Record Macro to find out the correct syntax
Pivot tables code can be a bit alien
But record a macro as you filter manually and it is a very good start
This is what it gave me ...

Sub Macro2()
'
' Macro2 Macro
'
    ActiveSheet.PivotTables("Review").PivotFields("Qtr").ClearAllFilters
    ActiveSheet.PivotTables("Review").PivotFields("Qtr").CurrentPage = "Q1"
End Sub

 

[KimC2504]

Hi Yongle,
I have actually been trying all afternoon everything your have suggested. I think the problem is I am tricking excel into thinking the pivot table is a table to allow me to filter

I have added in filters in the value columns eg A&P, Attach, GL, OS & Total as I like to be able to filter the total>50

When I record the macro I get the following code:

 ActiveSheet.Range("$B$21:$O$2353").AutoFilter Field:=1, Criteria1:="Q1"
    ActiveSheet.Range("$B$21:$O$2353").AutoFilter Field:=13, Criteria1:=">50", _
        Operator:=xlAnd

When I run the code, I get "AutoFilter method of Range class failed.

And yes my PivotTable is named Review

 

[Yongle]

Pivot tables work one way and standard ranges work another
- it does not surprise me that you are encountering problems with what you are trying to do

Consider inserting an intermediate step in the process
- use VBA to copy/paste the values returned by pivot table to a new sheet and do your filtering there
- pivot table remains intact for future use

See this excellent guide to Referencing Pivot Table Ranges in VBA

 

[KimC2504]

It is a tricky one, so much for the simple macro my manager asked for !

I will have a think about it. I really don't want to copy/paste values to a new sheet. I am trying to keep this file pretty simple.

Thanks for your help Yongle