Unable to get the PivotFields property of the PivotTable class

[Opal]

I have been struggling to get this to work for a couple of days now. I have been reading posts but nothing seems to solve the issue I am having so I am hoping someone out there can see what I cannot see. I am using Excel 2016 and working with multiple pivot tables. What I want to do is that when the value changes in a cell outside of the Pivot Table I want the filter on the column to update with the new value. So basically I have source data for three pivot tables on one worksheet tab. From there I calculate the top 3 injured body part from this data and then each pivot table is to filter with the Top1, 2 , 3 showing the area in the shop and the work process that the employee was on where the injury occurred. However, when I attempted to refresh the data I get the error: Unable to get the PivotFields property of the PivotTable class

Here is my code for one of the Pivot Tables:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, 'it only updates when cell'B4
'or B5 is touched

If Intersect(Target, Range("B1:C1")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable
Dim FieldInjuryTop1 As PivotField
Dim NewInjuryTop1 As String

'Amend here to filter your data
Set pt1 = Worksheets("Pivot Table Top1").PivotTables("PivotTable1")
Set FieldInjuryTop1 = pt1.PivotFields("Count of Injury Map Body Part")
NewInjuryTop1 = Worksheets("Weekly TIR Summary Data").Range("TopN1").Value

'This updates and refreshes the PIVOT table
With pt1
FieldInjuryTop1 = NewInjuryTop1
FieldInjuryTop1.CurrentPage = NewInjuryTop1
pt1.RefreshTable
End With

End Sub

This line: Set FieldInjuryTop1 = pt1.PivotFields("Count of Injury Map Body Part")

is what causes the error. Any assistance, links, advice you can provide would be appreciated. Thank you.

 

[RoryA]

Hi,

The error means that that isn't the name of the pivot field.

 

[Opal]

this is where I get confused.... because I changed it to: Injury Map Body Part and get the same error. The column head on the table where the data that sources the pivot table resides it is clearly typed: Injury Map Body Part

there are no leading spaces, no ending spaces....

 

[Marcelo Branco]

Maybe...

Set FieldInjuryTop1 = pt1.[COLOR=#0000ff]DataFields[/COLOR]("Count of Injury Map Body Part")

M.

 

[Opal]

Sorry, no... same error.

 

[Marcelo Branco]

Sorry, no... same error.

hmm...worked for me with a very simple pivot table

Raw data

[TABLE="class: grid"]
<tbody>[TR]
[TD]

Name​

[/TD]
[TD]

Injury Map Body Part​

[/TD]
[/TR]
[TR]
[TD]

Anthony​

[/TD]
[TD]

A1​

[/TD]
[/TR]
[TR]
[TD]

Anthony​

[/TD]
[TD]

A2​

[/TD]
[/TR]
[TR]
[TD]

Bob​

[/TD]
[TD]

A3​

[/TD]
[/TR]
[TR]
[TD]

Bob​

[/TD]
[TD]

A4​

[/TD]
[/TR]
[TR]
[TD]

Mary​

[/TD]
[TD]

A5​

[/TD]
[/TR]
[TR]
[TD]

Mary​

[/TD]
[TD]

A6​

[/TD]
[/TR]
[TR]
[TD]

Richard​

[/TD]
[TD]

A7​

[/TD]
[/TR]
</tbody>[/TABLE]


Pivot Table

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Name​

[/TD]
[TD]

Count of Injury Map Body Part​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Anthony​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Bob​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Mary​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Richard​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Grand Total​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub aTest()
    Dim pt1 As PivotTable
    Dim FieldInjuryTop1 As PivotField

    Set pt1 = Worksheets("Pivot Table Top1").PivotTables("PivotTable1")
    Set FieldInjuryTop1 = pt1.DataFields("Count of Injury Map Body Part")
    Debug.Print FieldInjuryTop1.DataRange.Address
End Sub

M.

 

[Opal]

I appreciate your patience... same error - Unable to get the DataFields property of the PivotTable class

The data source is formatted as a Table - this wouldn't be causing the issue, would it?

 

[Marcelo Branco]

I can't see any problem with data as a table.
If the field Injury Map Body Part is a DataField (that is, a field dragged to Values area when building the pivot table) and the name of the field is correct (no spaces, no extraneous characters...) it should have worked.
I don't know how to help more...

M.

 

[Opal]

thanks for trying, I appreciate it.

 

[Opal]

So I pulled all of the data into a brand new workbook to see if I could figure out what's what. And when I converted the raw data into a table, I got the same error. However, before I converted the data into a table I got a different error... but I can't see to re-create it if I convert back to a data range.