Can't get simple pivot table macro to work?

[hmmmidk220]

I'm trying to update the month of a pivot table based on what is in cell H6. The data source has a month column where the data is stored as text like so: May-2017.

This is what I'm trying to work from:

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("PivotTable12")
Set Field = pt.PivotFields("Invoice Month")
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

Pivot table 12 is the correct name for the PT, Invoice month is the correct category name, and I named the sheet Sheet1 so everything matches there.

The error it gives is: Run time error 1004 unable to set currentpage property of the pivotfield class. Debug points to the "Field.CurrentPage = NewCat" line.

My pivot table has 4 row fields and one value field, where it is the sum function.

Is this enough info to help?

 

[Worf]

o I am assuming you are talking about a row field.
o One option would be moving it to the filters area.
o Another one is to use a slicer as shown below.

Dim f$, sc As SlicerCache


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim si As SlicerItem
If Intersect(Target, [H6]) Is Nothing Then Exit Sub
sc.ClearAllFilters
For Each si In sc.SlicerItems
    If si.Name = Me.[H6] Then
        si.Selected = True
    Else
        si.Selected = False
    End If
Next
End Sub


Private Sub Worksheet_Activate()
f = "Department"                                                ' desired field
On Error Resume Next
Set sc = ActiveWorkbook.SlicerCaches(f)
If Err.Number <> 0 Then
    Set sc = ActiveWorkbook.SlicerCaches.Add2(Me.PivotTables("PivotTable2"), f)
    sc.Name = f
    sc.Slicers.Add Me, , f, f, 120, 1220, 150, 200              ' create slicer if needed
End If
End Sub