Unable to get the PivotFields property of the Pivot Table Class

[Vanessa Maynaes]

Hello,

I am trying to control a pivot table from the dynamic cell value in E2, supervisor name. However, when I run the code below, I get unable to get the PivotFields property of the Pivot Table Class error. At this point, I am unsure what is causing this code. There are no trailing spaces in the field name and as far as I can see there's no error. Please help!


Here's a screenshot of my worksheet. I need A to populate B.

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("D1")) Is Nothing Then Exit Sub

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

'Here you amend to suit your data
Set pt = Worksheets("Report(Mgr)").PivotTables("IncompCourse")
Set Field = pt.PivotFields("Supervisor Name")
NewSupv = Worksheets("Report(Mgr)").Range("E2").Value

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

End Sub

 

[xlstime]

Please upload sample data set.

Hello,

I am trying to control a pivot table from the dynamic cell value in E2, supervisor name. However, when I run the code below, I get unable to get the PivotFields property of the Pivot Table Class error. At this point, I am unsure what is causing this code. There are no trailing spaces in the field name and as far as I can see there's no error. Please help!


Here's a screenshot of my worksheet. I need A to populate B.

View attachment 33461

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("D1")) Is Nothing Then Exit Sub

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

'Here you amend to suit your data
Set pt = Worksheets("Report(Mgr)").PivotTables("IncompCourse")
Set Field = pt.PivotFields("Supervisor Name")
NewSupv = Worksheets("Report(Mgr)").Range("E2").Value

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

End Sub

 

[Vanessa Maynaes]

The file was to large to upload so I placed it on my google drive located here:

https://drive.google.com/open?id=0BwKPcxH38TZoY3IzWlpqWVBDZjg

______________________________________________________________

Mod edit : thread moved to appropriate forum !​