Adding New Data Field Error

[no1gdog]

I am trying to write a macro that adds a new month of data to the Values section of a pivot table. Every month I need add the new month's data to the pivot report. My issue is I have a field called YTD that I need the new month's data to come before it. Currently the code put it after the YTD so I'll have Month 1-8 then YTD total when I run my macro to add Month 9 it puts if after YTD so I end up with Month 1-8 then YTD then Month 9.

The first macro I wrote I put the new month field in front of the YTD field in the Pivot but when I re-run it the macro does what I described above:

Sub AddNewMonth()
'
' AddNewMonth Macro
'


'
Sheets("App Services").Select
Range("C189").Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("008.2018"), "Count of 008.2018", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of 008.2018")
.Caption = "Sum of 008.2018"
.Function = xlSum
End With


So I re-wrote the macro to add the new month's field after the YTD field, then select the field again and move it before the YTD field but then I get an error:

Sheets("App Services").Select
Range("C217").Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("009.2018"), "Count of 009.2018", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of 009.2018")
.Caption = "Sum of 009.2018"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("YTD"), "YTD", xlSum

The error is Run-time error 5: Invalid call or argument and it highlights this part of the code (which is part where I move the new month's field before the YTD field):

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


Can anyone help me accomplish this? I will then need to copy the code to several sheets 26 times so it will be a time saver!



Question two:


Is there a way to add a user selection pop up to change anywhere that it says the month.year (009.2018) to any selected month? So I can run the macro a pop up tells me to select the month and the code will select that month's data so next month I'll do 010.2018 and run the macro to add the new month's data before the YTD field so I get Month 1-10 then YTD.

Thank you!