Filtering between two dates in pivot table using VBA. UK to US date format issue

Indeed, this is strange. I've reproduced your issue with Excel 2007 and German locale settings.

However, when converting the date values to Longs using CLng, everything works:

ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=CLng(Range("DateFrom").Value), Value2:=CLng(Range("DateTo").Value)