Code Question

[UpTheArgyle]

Has anyone got any idea why this does not work?

I am trying automated "date's between" in a pivot table via a command button on a userform


ActiveSheet.PivotTables("PivotTable5").PivotFields("calendar_date"). _
PivotFilters.Add Type:=xlDateBetween, value1:=Range("B1"), Value2:=Range("B2")

Run Time Error 1004 - "The date you entered is not a valid date"

In Cells B1 & B2 is this format 10/02/2011 - both cells are formatted as a date

any ideas?

 

[kpark91]

Hello, UpTheArgyle

How about instead of directly calling the values, setting them as string or date variables and then calling them?

like

Dim str1 As String, str2 As String

str1 = Range("B1").Value
str2 = Range("B2").Value

PivotFilters.Add Type:=xlDateBetween, value1:=str1, Value2:=str2

 

[mikerickson]

Try

value1:=DateValue(Range("B1").Text), Value2:=DateValue(Range("B2").Text)

 

[UpTheArgyle]

Unfortunately neither works