Write Cell Value to Pivot Table - Unable to get the PivotFields property error

[vmaynard25]

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 the link to the file: https://drive.google.com/open?id=0Bw...3IzWlpqWVBDZjg

Here's a screenshot of my worksheet. I need A to populate B.
2016-08-09_1022.png

 
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

[AlphaFrog]

It looks like "Supervisor Name" is part of a Slicer and not the Pivot Table. Record a macro where you change the name to get example code.

[vmaynard25]

"Supervisor Name" is in the filter area not a slicer. I assumed that would make it part of the the PivotTable.PivotFields. Ultimately, I just need the text in E2 to populate the "Supervisor Name" filter so that will filter the table.

[AlphaFrog]

"Supervisor Name" is in the filter area not a slicer. I assumed that would make it part of the the PivotTable.PivotFields. Ultimately, I just need the text in E2 to populate the "Supervisor Name" filter so that will filter the table.

What code do you get when rerecording a macro and changing the "Supervisor Name" field? Is it a PageField?

[vmaynard25]

I haven't wrote any new macro yet. I guess I don't understand why I would need to write a macro to change the name when the Supervisor Name is part of the Pivot Field. Can you please explain? I am sorry. I'm so lost on this error.

[AlphaFrog]

I haven't wrote any new macro yet. I guess I don't understand why I would need to write a macro to change the name when the Supervisor Name is part of the Pivot Field. Can you please explain? I am sorry. I'm so lost on this error.

I'm simply saying record a macro and change the Supervisor Name to get the example code that shows you how to reference that field within the code. It takes all of two seconds to record a macro. You don't have to write a thing.

[xlnitwit]

Hi,

That is not the correct way to refer to a dimension

Field.CurrentPage = "[Assigned].[Supervisor Name].&[" & NewSupv & "]"

[vmaynard25]

I get the error even when I refer to it this way:

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("[Assigned].[Supervisor Name].[Supervisor Name]")
NewSupv = Worksheets("Report(Mgr)").Range("E2").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = Worksheets("Report(Mgr)").Range("E2").Value
pt.RefreshTable
End With

End Sub

[xlnitwit]

Does E2 contain something resembling [Assigned].[Supervisor Name].&[Smith, J.]?

[vmaynard25]

Yes, E2 contains a supervisor name just written in a cell value. See below:

2016-08-15_1656.png

[xlnitwit]

I fear you are not understanding. Either E2 must contain the full text:
[Assigned].[Supervisor Name].&[Miller, Rory L]

or the code must add the additional text as I suggested.

[vmaynard25]

When I changed the formula to the text that you suggested, I received the same error:

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("[Assigned].[Supervisor Name].[Supervisor Name]")
NewSupv = Worksheets("Report(Mgr)").Range("E2").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = "[Assigned].[Supervisor Name].&[" & NewSupv & "]"
pt.RefreshTable
End With

End Sub

[vmaynard25]

I really need help here. Can anyone help me figure out what I am doing wrong?

[vmaynard25]

Thank you AlphaFrog and xlnitwit. AlphaFrog suggestion of recording the macro helped me clearly identify the Pivot Table fields and Xlnitwit earlier suggestions lead me in the right direction of capturing the cell value. Since I was having trouble with the current page property error, I decided to use a macro instead which writes the cell value and then select it from the pivot table drop down list that contains Supervisor Names. The code is simple and gets me what I need. I also I got it to work with the code I posted earlier but since I am working with a lot of data it took a long time to calculate with each mouse click.

Here is my simple macro:

Sub GoToMgrReport()
    Worksheets("Report(Mgr)").Activate
    
    'Assign cell value as new Supervisor Name
    Dim NewSupv As String
    NewSupv = Range("E2").Value
    
    'Select the new supervisor name from the drop down list in the Pivot Table
    ActiveSheet.PivotTables("IncompCourse").PivotFields( _
        "[Assigned].[Supervisor Name].[Supervisor Name]").VisibleItemsList = Array( _
        "[Assigned].[Supervisor Name].&[" & NewSupv & "]")
End Sub