PDA

View Full Version : Specifying filter according to date



anne.gomes
06-08-2014, 08:09 PM
Hi,

I am trying to only filter two dates in the current pivot table. So only two dates should be ticked in the filter.

Here is my code, I keep getting an error.

Can anyone please help?





Sub Macro5()
'
' Macro5 Macro
'
'

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 2)
End With

End Sub



Thank you

Aussiebear
06-08-2014, 09:43 PM
What is the error message?

anne.gomes
06-08-2014, 10:02 PM
Object doesn't support the property of method for:



.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 2)

mancubus
06-09-2014, 12:14 AM
it's all about non-US date formats in VBA.

you want to filter pivot items between two dates.
i modified what macro recorder gave me to handle vba date problem.



Sub FilterPT()
Dim dtBeg As Date, dtEnd As Date

dtBeg = DateSerial(Year(Date), Month(Date), Day(Date))
dtEnd = DateSerial(Year(Date + 2), Month(Date + 2), Day(Date + 2))

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.ClearAllFilters
.PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(dtBeg), Value2:=CLng(dtEnd)
End With
End Sub

anne.gomes
06-09-2014, 01:30 PM
Hi mancubus, thanks for the quick response. I get an error on the line :



.PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(dtBeg), Value2:=CLng(dtEnd)


It says application defined or object defined error.

Any ideas?

mancubus
06-10-2014, 12:15 AM
you are welcome.

attached is a working example.

anne.gomes
06-10-2014, 01:37 PM
Thanks mancubus,

I still get an error on the same line, :




.PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(dtBeg), Value2:=CLng(dtEnd)

:( it says application defined or object defined error

mancubus
06-10-2014, 02:08 PM
you are welcome.

post your workbook with fake data please.

anne.gomes
06-10-2014, 07:59 PM
I tried putting my workbook but it is too big :( I copied and pasted your exact code so I duno why it isn't working. The only thing I can think of it not working is because the pivot table is linked to a pivot chart. So when I change filter in the table it automatically adjusts the chart. Is this the reason why I get an error?

Thanks mancubus

mancubus
06-10-2014, 11:04 PM
you're welcome.

no, a chart based on pivot table does not affect the macro.

just keep 20-30 rows of data (include some rows whose dates meet the filter criteria) in your workbook then try posting here.

anne.gomes
06-11-2014, 08:13 PM
Hi,

I get an error on the line ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").AutoSort _
xlDescending, "Date"
I shouldn't get an error because I recorded this Macro...



Sub Workbook_Open()
'
' Macro1 Macro
'
'
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").AutoSort _
xlDescending, "Date"

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlBottomCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Count of ID"), Value1:=4

ActiveWorkbook.Save
End Sub


Any Help?