Filter in excel 2010 pivot on last 3 months with VBA

Question

There are several Excel sources (xxx; yyy; zzz;...), all with the same format, all huge, around 200.000 lines. I need to create a separate statistics file, one sheet for all source files, same statistics on each sheet, several pivots on each sheet. The source files are growing each week.

I can make my macro update the source of the pivots. I need the pivots to show the last 3 months only.

I can add one column to the source files, but these are used by different agents and the column might be cancelled by them so my pivot won't show the last 3 month any more.

I would need that the macro filters the pivot on the last 3 months without touching the source file. I also don't want to copy the sources in the statistics file because it would become too huge this way.

I have found a solution which filters between dates in pivot with macro, but it is filtering between 2 fixed dates, not the last 3 month.

Sub PivotTableFilter()
 Dim PvtTbl As PivotTable

 Set PvtTbl = Worksheets("Sheet12").PivotTables("PivotTable1")
'delete all filters currently applied to the PivotTable, using the PivotTable.ClearAllFilters Method
 PvtTbl.ClearAllFilters
 PvtTbl.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, Value1:="4/28/2009", Value2:="5/20/2009"
End Sub

I tried to replace it by today and today-90 days, but it did not work (and also I don't want to delete all filters, I want to keep the others.)

Annamaria Tokes Annamaria Tokes · Accepted Answer · 2015-12-27T22:27:14

I tried with the suggestion as follows (pivot is on Sheet1, and this time used only one pivot), but it is not ok :( What is wrong?

Sub PivotTableFilter_3months() Dim PvtTbl As PivotTable

Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

PvtTbl.PivotFields (DateSerial(Year(Date), Month(Date) - 3, Day(Date)))

End Sub

Thanks!

Filter in excel 2010 pivot on last 3 months with VBA

1 Answers