VBA to Add a New Field to my Pivot

no1gdog

New Member
Joined
May 5, 2011
Messages
13
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Anyone out there that could help me with this? I usually get a great response when I post. Maybe I need to explain a little clearer?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top