Run-time Error 5: Invalid procedure call or argument

[no1gdog]

I wrote a pivot to add a new field to a pivot table but it's adding the field to the end of the other fields (Pivot has Month 1 - 8 and a YTD field) I want to add Month 9 so that I get Month 1 - 9 and a YTD field that adds up Months 1 to 9. When I record the Macro and place Month 9 where I want it (before the YTD field) I then run the macro and it puts Month 9 after the YTD field so I get Month 1-8 then YTD then Month 9.

I tried to record the macro to place it after YTD then reselect Month 9 and move it before YTD (so I get Month 1-9 then YTD) but when I run it I get an error. Can anyone help with a solution?

Here's the VBA:

Range("E190").Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("009.2018"), "Count of 009.2018", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of 009.2018")
.Caption = "Sum of 009.2018"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("YTD"), "YTD", xlSum


Here's what it's selecting as the error as a Run-time Error 5: Invalid procedure call or argument

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("YTD"), "YTD", xlSum

 

[Jerry Sullivan]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1053391-vba-add-new-field-my-pivot.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.