VBA macro to format Pivot table fields

[JPM]

I am trying to write a macro which would format all pivot table fields that start with "Count of" as a number and all that start with "Sum of" as currency.

This is the code I have so far, but I doesn't quite work:

Sub FormatPivot1()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

pt.PivotFields("Count of " & "*").NumberFormat = "###0"
pt.PivotFields("Sum of " & "*").NumberFormat = "$ #,##0"

Range("B26").Select
End Sub

Does anyone know the proper syntax that would make it work?
Thanks

 

[Domenic]

Try...

Sub FormatDataFields()

    Dim pf As PivotField
    
    For Each pf In ActiveSheet.PivotTables(1).DataFields
        Select Case pf.Function
            Case -4112 'xlCount
                pf.NumberFormat = "###0"
            Case -4157 'xlSum
                pf.NumberFormat = "$ #,##0"
        End Select
    Next pf
    
End Sub

Hope this helps!

 

[JPM]

Domenic, this is exactly what I was looking for.
Thanks.

 

[Domenic]

You're very welcome!