Batch Value Field Settings

 

Copy and paste this VBA Code for Averages with NO decimal places:

 

Public Sub SetDataFieldsToAverage()

'Update 20141127

Dim xPF As PivotField

Dim WorkRng As Range

Set WorkRng = Application.Selection

With WorkRng.PivotTable

   .ManualUpdate = True

   For Each xPF In .DataFields

     With xPF

         .Function = xlAverage

         .NumberFormat = "#,##0"

     End With

   Next

   .ManualUpdate = False

End With

End Sub

 

Copy and paste this VBA Code for Average Costs with standard currency:

Public Sub SetDataFieldsToAverage()

'Update 20141127

Dim xPF As PivotField

Dim WorkRng As Range

Set WorkRng = Application.Selection

With WorkRng.PivotTable

   .ManualUpdate = True

   For Each xPF In .DataFields

     With xPF

         .Function = xlAverage

         .NumberFormat = "$#,##0.00"

     End With

   Next

   .ManualUpdate = False

End With

End Sub