Filtering Pivot table by cell values, error 1004 V

Question

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

Answer 1

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.

Answer 2

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)