Pivot filter on date with xlAfterOrEqualTo and xlAfter and others generate error

[Invisibleman]

Dear all,

I try to set the date filter on the Pivot thru VBA. But if I do try with the date, this is generating an error, where I don't know why/how to solve.

I have extra set the cell value to a date in the code, but this also doesn't help.

My code is;

Private Sub box47_6_SelectCrypto_Change()
Dim startdate As Date
startdate = New_Chart_DailyCrypto.Range("K1").Value
   With New_Pivot_DailyExchange.PivotTables("Pivottable1").PivotFields("Crypto")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlCaptionContains, Value1:=box47_6_SelectCrypto.Value
        .PivotFilters.Add Type:=xlAfter, Value1:=startdate
End With

End Sub

And the error I am getting is;

But to be honest, when searching the web, I don't see what I am doing wrong.
I also did try, for testing, to only filter on the xlafter, so disable the xlCaptionContains, but still I am receiving the error.

If you can help, that would be very appriciated,

Regards,
Hans

 

[Invisibleman]

Dear all,

I think found out why the error did occur.

It change the code so, that the clearallfilters is set per field.

However, after this I received the problems with the source date (DD/MM/YYYY format) when got the 13/01/2024, it did say invalid date.
So on the internet I then found to use the CLng(Startdate), which indeed also solved this problem.

If I can improve anything then please let me know?
Below the new code I have, this also to help others, running into the same issues.

Private Sub box47_6_SelectCrypto_Change()

Dim startdate As Date

startdate = New_Chart_DailyCrypto.Range("K1").Value2

   With New_Pivot_DailyExchange.PivotTables("Pivottable1")
        .PivotFields("Crypto").ClearAllFilters
        .PivotFields("Crypto").PivotFilters.Add Type:=xlCaptionContains, Value1:=box47_6_SelectCrypto.Value
        .PivotFields("Start Date").ClearAllFilters
        .PivotFields("Start Date").PivotFilters.Add Type:=xlAfter, Value1:=CLng(startdate)
   End With

End Sub

Regards,
Hans