How To Change What A Pivot Tables Column Filter Is Called Vba

Yongle

Well-known Member

Joined

Mar 11, 2015

Messages

6,977

Office Version

1. 365

Platform

1. Windows

• #2

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

KimC2504

Board Regular

Joined

Jul 17, 2012

Letters

141

• #three

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

Code I used:

VBA Code:

                        Sub ReportFiltering_Single() 'PURPOSE: Filter on a single item with the Report Filter field 'SOURCE: www.TheSpreadsheetGuru.com  Dim pf Equally PivotField  Prepare pf = ActiveSheet.PivotTables("Review").PivotFields("Qtr")  'Clear Out Whatever Previous Filtering   'pf.ClearAllFilters  'Filter on 2014 items   pf.CurrentPage = "Q1"  End Sub                      

Yongle

Well-known Member

Joined

Mar eleven, 2015

Letters

half dozen,977

Role Version

1. 365

Platform

1. Windows

• #iv

The code works fine
- is your pivot tabular array named Review ?
- is your pin table set with Qtr as i of the filters ?

Code used (identical to yours):

VBA Code:

                          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 employ of Tape Macro to find out the correct syntax
Pivot tables code can be a bit alien
But record a macro every bit you filter manually and it is a very proficient start
This is what information technology gave me ...

VBA Lawmaking:

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

Final edited: Dec 5, 2019

KimC2504

Lath Regular

Joined

Jul 17, 2012

Messages

141

• #5

Hi Yongle,
I take really been trying all afternoon everything your have suggested. I call up the problem is I am tricking excel into thinking the pivot tabular array is a table to allow me to filter

I have added in filters in the value columns eg A&P, Adhere, GL, Bone & Total as I like to be able to filter the full>fifty

When I tape the macro I get the following code:

VBA Lawmaking:

                                                      ActiveSheet.Range("$B$21:$O$2353").AutoFilter Field:=i, 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 form failed.

And yes my PivotTable is named Review

Final edited: Dec 5, 2019

Yongle

Well-known Fellow member

Joined

Mar 11, 2015

Messages

6,977

Office Version

1. 365

Platform

1. Windows

• #6

Pivot tables work i way and standard ranges work some other
- it does not surprise me that you are encountering issues with what you are trying to practice

Consider inserting an intermediate pace in the process
- employ VBA to copy/paste the values returned by pivot table to a new sheet and practice your filtering there
- pivot table remains intact for time to come utilize

Run into this excellent guide to Referencing Pin Table Ranges in VBA

KimC2504

Board Regular

Joined

Jul 17, 2012

Messages

141

• #7

Information technology is a catchy one, and then much for the uncomplicated macro my director asked for !

I will take a think most it. I really don't want to copy/paste values to a new sail. I am trying to continue this file pretty unproblematic.

Thanks for your help Yongle

Source: https://www.mrexcel.com/board/threads/vba-to-filter-pivot-table.1117010/

Posted by: teskefrousess.blogspot.com

Share this post