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 !!

This works for me:

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

--
HTH,
Bernie
MS Excel MVP


"Jean-Marc P" wrote in message
...
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 !!

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.

"Bernie Deitrick" wrote:

This works for me:

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

--
HTH,
Bernie
MS Excel MVP


"Jean-Marc P" wrote in message
...
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" wrote:

This works for me:

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

--
HTH,
Bernie
MS Excel MVP


"Jean-Marc P" wrote in message
...
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 !!

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/comm...b-937ad3c07c79 )

--
KenY


"Jean-Marc P" wrote:

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 !!

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(TODA Y()) +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" wrote in message
...
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.

"Bernie Deitrick" wrote:

This works for me:

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

--
HTH,
Bernie
MS Excel MVP


"Jean-Marc P" wrote in message
...
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 !!

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

"KenY" wrote:

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/comm...b-937ad3c07c79 )

--
KenY


"Jean-Marc P" wrote:

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 !!