VBA Error while updating pivot table filter to show current month's data only

Question

This seems like a simple problem, but I've spent hours failing to solve... I'd like to have my pivot table update an existing filter to include all dates this month. I regularly update the data source and this source includes data from year to date. In my pivot table, I only want to see this month's data. So I wrote this:

Dim pvtAOL As PivotTable
Set pvtAOL = ActiveSheet.PivotTables(1)

pvtAOL.TableRange1.Activate
pvtAOL.RefreshTable
pvtAOL.PivotFields("Date").ClearAllFilters
pvtAOL.PivotFields("Date").PivotFilters.Add FilterType:=xlAllDatesInPeriodJuly

If I comment out the last line, everything works. If I try to run it with the last line included, I get the following error:

Run-time error '448':Named argument not found

Open to other fixes / corrections if you see them as I'm new to VBA. thx!

what about xlDateThisMonth? Also does excel recognize the Date PivotField as an actual Date value? Find enumeration here .. if it helps. — Scott Holtzman
I am uploading a file named 01-08-14.xlsm. This is for a date period range which can be modified for your purpose suitably. — skkakkar
@ScottHoltzman, xlDateThisMonth gives the same error. I've visited the enumeration page but not really sure how to use it to solve this problem. I've wondered if I need to stipulate the date format in someway? the raw data uses mm/dd/yyyy — Kent Sanner
I am finding Microsoft's documentation a bit confusing. It says the "Name" of the parameter is Type, but the examples use FilterType. When you record a macro doing a similar thing, the created code uses Type. Maybe they are synonyms, but it might be worth trying to change FilterType to Type. — YowE3K
@YowE3K, I noticed the same thing. Most-all the examples of similar code I've found use FilterType and using Type gives me a different error: Run-time error 1004: Application-defined or object-defined error — Kent Sanner

YowE3K YowE3K · Accepted Answer · 2016-07-29T20:11:36

Microsoft's documentation is a bit confusing. It says the "Name" of the parameter is Type, but the examples use FilterType.

When you record a macro doing a similar thing, the created code uses Type.

I have done some testing and get the same error as the OP when using FilterType but using Type works.

I suggest changing your code from

pvtAOL.PivotFields("Date").PivotFilters.Add FilterType:=xlAllDatesInPeriodJuly

to

pvtAOL.PivotFields("Date").PivotFilters.Add Type:=xlAllDatesInPeriodJuly

To clear existing filters from the "Date" field so that you can add a new filter, use

pvtAOL.PivotFields("Date").ClearLabelFilters

(Or use .ClearAllFilters if you prefer.)

VBA Error while updating pivot table filter to show current month's data only

1 Answers