PivotTable Date Range on Row Fields

[mistryman]

Hey Guys

The code below keeps telling me that the date I entered is not a valid date. I checked the cells and everything matches up. Anyone have an idea of what might be wrong.

Sub Macro1()
'
' Macro1 Macro
'
Dim dtStart As Date, dtEnd As Date
  
  With Worksheets("Pivot")
  dtStart = Range("E1")
  dtEnd = Range("F1")
  
  With .PivotTables("MatDataPvt").PivotFields("MaturityDate")
  .ClearAllFilters
  .PivotFilters.Add Type:=xlDateBetween, _
  Value1:=dtStart, Value2:=dtEnd
  End With
  
  End With

End Sub

 

[jeffreyweir]

Date formats in PivotTables can be very tricky - particulary if you use a non US date format.
I wrote something on this at http://dailydoseofexcel.com/archives/2014/05/21/learning-from-my-errors/
that might be worth a read.

Fire up the macro recorder, set up the above manually, and post the code back here, and I'll take a look.