Programming the filter of a pivot table

[Jean-Marc P]

I just can't get through this ... I've spent hours on it & I'm sure it's very
simple !!

I download a (.txt) file with tons dates of events in it. Once this is done,
i run a macro that does numerous things, including filter the list & then
refresh the pivot table. Info that have been filtered in the list still
appear in the pivot table. How can I get rid of them ? (I join the part of
the code where I filter & refresh)

'filter the list to get the next 4 months
Selection.AutoFilter Field:=9, Criteria1:=">=today()", Operator:=xlAnd _
, Criteria2:="<=TODAY()+122"
'refresh the pivottable
Sheets("Dynamique").Select
ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh

Please help me !!

 

[Bernie Deitrick]

This works for me:

Selection.AutoFilter Field:=9, Criteria1:=">=" & Now(), Operator:=xlAnd, _
Criteria2:="<=" & Now() + 122

 

[Jean-Marc P]

The problem isn't to filter the list. Both your code & mine works fine. But
either way, when I update the pivot table related to that list, filtered &
unfiltered lines all show into the pivot table. I only want the ones in the
right date range.

 

[Jean-Marc P]

This works for me:

Selection.AutoFilter Field:=9, Criteria1:=">=" & Now(), Operator:=xlAnd, _
Criteria2:="<=" & Now() + 122

 

[KenY]

does your pivot table filter correctly if you filter manually?

Are your dates in the right format and are they properly recognised as Excel
dates? I am wondering if there is a link with the problem that I am
experiencing (see my post 'Texttocolumns and Date Format using Macro - UK
International setti ngs'
http://www.microsoft.com/office/com...&p=1&tid=3765f273-b823-4bc2-a67b-937ad3c07c79 )

 

[Bernie Deitrick]

JEan-Marc,

Sorry for misunderstanding your question.

Insert another column into your database, and use a formula like

=AND(I2>=TODAY(),I2<=DATE(YEAR(TODAY()),MONTH(TODAY()) +4,DAY(TODAY())))

(this is assuming that your dates are in column I)

Then use that column (which will return TRUE and FALSE) as either a row or page filter, and select
only TRUE.

In code, you could do that like this:

Range("J1").Value = "Include"
Range("J2:J" & Cells(Rows.Count, 9).End(xlUp).Row).FormulaR1C1 = _
"=AND(RC[-1]>=TODAY(),RC[-1]<=DATE(YEAR(TODAY()),MONTH(TODAY()) +4,DAY(TODAY())))"

which will put that formula into column J, matching the entries in column I.

HTH,
Bernie
MS Excel MVP

 

[Jean-Marc P]

Manual filter (auto filer) works fine !?!? Doesn't seem to be the same pb