Macro to format Pivot Data field

[uncled]

I spend a substantial amount of time working with pivot tables on a daily basis and find myself constantly having to re-format the pivot data field using the Field Settings. The default setting appears to be General and I am constantly changing to currency with no decimals.

I'd love to be able to create a generic macro (i.e. pivot data field is not the same for all my pivot tables) that would allow me to assign a shortcut key to quickly change the format. I've managed to create a macro to do this, but it is hard-coded with the name of the pivot data field.

Is there a "generic" term I can apply (i.e. PivotFields(DataField)) that i can reference in the code.

Thanks for any assistance...this would save me a ton of time/mousclicks!!!

-Ed

 

[Gavona]

Here is the macro I use to do that

Sub PvtFieldsSumComma()
OldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim PvtField As PivotField
[red]For Each PvtField In Selection.PivotTable.DataFields[/red]
    With PvtField
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Caption = Replace(.Caption, "Sum of", "")   'replace sum of
    End With
Next PvtField

Application.Calculation = OldCalc
Application.ScreenUpdating = True
End Sub

The red row answers your question.

Regards,

Gavin

 

[uncled]

Many, Many, Thanks Gavin!!! - didn't think of the looping technique...works like charm.

-Ed