How to control Excel PIVOT tables from a cell(s) value with VBA

[The Cruncher]

I am creating a dashboard and need to be able to update multiple pivotcharts using a date range that comes from daily dates but is then grouped by month so that I can choose the month and year from a cells that are set up as data validation lists in two seperate cells and have the pivot table update that will then drive a pivot chart.

Initially I found the following code:
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("H6:H7")) 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("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range("H6").Value

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

End Sub



I then tried to modify it as follows for date referencing and it did not work;

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, Worksheets("DashBoard Data").Range("M37:M39")) Is Nothing Then Exit Sub
End If
'Set the Variables to be used
Dim pt As PivotTable
Dim Field1 As PivotField
Dim Field2 As PivotField
Dim Field3 As PivotField
Dim Field4 As PivotField

Dim NewCat1 As String
Dim NewCat2 As String
Dim NewCat3 As String
Dim NewCat4 As String

'Here you amend to suit your data
Set pt = Worksheets("Dashboard Data").PivotTables("PivotTable4")
'Set Field = pt.PivotFields("Category")
Set Field1 = pt.PivotFields("Years")
Set Field2 = pt.PivotFields("OPEN DATE")
Set Field3 = pt.PivotFields("CONTRACT TYPE")
Set Field4 = pt.PivotFields("STATUS")

NewCat1 = Worksheets("Dashboard Data").Range("M37").Value
NewCat2 = Worksheets("Dashboard Data").Range("M38").Value
NewCat3 = "Proposal"
NewCat4 = "Open"

'This updates and refreshes the PIVOT table
With pt
Field1.ClearAllFilters
Field2.ClearAllFilters
Field3.ClearAllFilters
Field4.ClearAllFilters
Field1.CurrentPage = NewCat1
Field2.CurrentPage = NewCat2
Field3.CurrentPage = NewCat3
Field4.CurrentPage = NewCat4
pt.RefreshTable
End With

End Sub

Any input would be appreciated it appears that when I get to trying to set the year month into two different cells the top script does not work.