DateBetween, change from a date to a value from a cell

[VBAProIWish]

Hello all,

Can someone change the code below so that...

Value 1 = Value from the date in cell "AA2" in the "C" worksheet
Value 2 = Value from the date in cell "AA4" in the "C" worksheet

My "laymens" way of illustrating it would be like this...
PivotFilters.Add2 Type:=xlDateBetween, Value1:=C!AA2, Value2:=C!AA4 <this is what I need code for

Sheets("Chart 2 MCDB").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LCD"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LCD"). _
        PivotFilters.Add2 Type:=xlDateBetween, Value1:="1/1/2020", Value2:="12/31/2020" '<<<this line

Thanks much!

 

[jasonb75]

Perhaps

PivotFilters.Add2 Type:=xlDateBetween, Value1:=WorkSheets("C").Range("AA2").Value, Value2:=WorkSheets("C").Range("AA4").Value

 

[VBAProIWish]

Hi Jason,
Thanks for that code, but I got the error below...

Run-time error '1004':
Application-defined or object-defined error

Any suggestions?

 

[jasonb75]

Try this instead, using .Value might be picking up the date serial instead of the actual date as a string.

PivotFilters.Add2 Type:=xlDateBetween, Value1:=WorkSheets("C").Range("AA2").Text, Value2:=WorkSheets("C").Range("AA4").Text

 

[VBAProIWish]

Thanks.
Now it's giving me an "object required"
error message

 

[jasonb75]

Did you just replace the last line of your old code with it? The 'Object required' error suggests that the line above it is missing or has no underscore at the end.
The full code for clarity, although you shouldn't need the first 2 lines.

Sheets("Chart 2 MCDB").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LCD"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LCD"). _
        PivotFilters.Add2 Type:=xlDateBetween, Value1:=WorkSheets("C").Range("AA2").Text, Value2:=WorkSheets("C").Range("AA4").Text

 

[VBAProIWish]

OMG, that is too funny, I just tried that and yes, you are correct!
I forgot the "clear filter" line!

THANKS SO MUCH, THIS IS PERFECT!

 

[jasonb75]

Simplified version (not tested but should work).

With WorkSheets("Chart 2 MCDB").PivotTables("PivotTable1").PivotFields("LCD")
        .ClearAllFilters
        .PivotFields("LCD").PivotFilters.Add2 Type:=xlDateBetween, Value1:=WorkSheets("C").Range("AA2").Text, Value2:=WorkSheets("C").Range("AA4").Text
End With

It is rarely necessary to select anything, using With blocks allows you to bypass that and make things run a lot quicker.
Selecting the chart didn't appear to be serving any purpose so I've left that out.

 

[VBAProIWish]

I will use that one!
Thanks again!