Unable to set the CurrebtPage property of the PivotField class

Hi,

I have some code to change the filter of a pivot table based off a date in a cell (H2). On a worksheet i have two identical pivots, in pivot1 (CurrentWk) i make a selection of the data i wish to see the data for, i want pivot2 (VsWeek) to show me the data for -7 days of the selected date in pivot1.

The date in the cell (H2) is -7 days of the selected date in pivot1.

Code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("H2:H3")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As Date

Set pt = Worksheets("HistoricalPivot").PivotTables("VsWeek")
Set Field = pt.PivotFields("[Historical Data].[Date].[Date]")
NewCat = Worksheets("HistoricalPivot").Range("H2").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

pt.RefreshTable

End Sub

The error happens at line highlighted in bold. I've done some looking around and all i can find is that it is something to do with the use of CurrentPage and the fileters i am using but i can't figure out a way around this.

Any help would be greatly appreciated.

Thanks,

Matt

I am not using US date format and can't seem to get the above to work.

Is there no way to get my current code working without the loop?