Filtering Pivot table by cell values, error 1004 VBA

0
votes

I have a pivot table where I would like to filter the invoice/despatch date based on a start and end date entered in 2 cells. I have the following code but keep getting an error "1004 - Application Defined or Object Defined error"

please help .. I'm using Excel 2016

Dim Invoice_Start_Date As Date
Dim Invoice_End_Date As Date
Invoice_Start_Date = CDate(Worksheets("Despatch Template").Cells(17, "F").Value)
Invoice_End_Date = CDate(Worksheets("Despatch Template").Cells(17, "G").Value)
Sheets("Despatch Template").Select

MsgBox IsDate(Invoice_End_Date)
MsgBox IsDate(Invoice_Start_Date)

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("DESPATCH 
DATE").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("DESPATCH 
DATE").PivotFilters.Add2 _
Type:=xlDateBetween, Value1:=CLng(Invoice_Start_Date), 
Value2:=CLng(Invoice_End_Date)

the msgbox returns TRUE just for testing dates.

I've tried just about everything I can find online, but it wont work. thanks

C

2
excelvbapivot-table

2 Answers

0
votes

The problem is that you're converting your date values to Long datatypes with CLng and then you're trying to date filter those Long values. Just delete CLng from this line:

Type:=xlDateBetween, Value1:=CLng(Invoice_Start_Date), Value2:=CLng(Invoice_End_Date)
0
votes

I found the answer.. it was due to the source data having non date values in some of the cells.. once I changed these for actual dates the code worked correctly.