Change Pivot Table Filter from Value in a Cell

Hi, apologies if you have seen this one before but I've been through about 10 posts and walkthroughs and cant get this to work!!

I'm a VBA newbie so don't have a great deal on knowledge on how to sort it but here's what I'm trying to do


I have 6 Pivot tables on Sheet 3 that are built to update a few charts on Sheet 2.

Sheet 1 contains all the raw data.


I want the user to be able to type in the date into a cell on Sheet 2 (cell "K1") and for that to update the 'Date' filter in PivotTable4. The value for the date in the Pivot Table is "B3" (Sheet3)

The code that I've been working on suggested activating a box to run the date change, to save it calculating all the time. I've got this cell as "N1". This is in Sheet 2, but I've not seen any mention of the sheet name in the code?

Anyway, the debugger is highlighting the row I have put in bold.

Can anyone point me in the right direction???? I'm sure once I have it working for this Pivot, I will be able to adapt it for all of them!!

Hope all that makes sense!!



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("N1")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Sheet3").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Date")
NewCat = Worksheets("Sheet3").Range("B3").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub