Formatting multiple pivot table fields at the same time

[stefansi]

Hi,

I'm working with a large pivot table which has multiple columns for different types of costs, revenues, margins etc. and I was wondering if I can format more than one field at the same time (they're all dollar formats) to save time?

I tried to google but didn't find my answer.

Many thanks,
Stefan

 

[pbornemeier]

This code should do as you asked.
Update the first Case statement to contain the names of the data fields that you DO NOT want to change formats for.

Option Explicit

Sub FormatPivotFields()

    Dim pf As PivotField
    
    For Each pf In ActiveSheet.PivotTables(1).VisibleFields

        'Debug.Print pf.Name, pf.Orientation
        If pf.Orientation = xlDataField Then
            Select Case pf.Name
            Case "NotME", "NotThisOne", "Name"
                'Won't change formats for datafields listed above
                'Add more quoted & comma separated fields as needed
            Case Else
                'Will change formats for all other data fields
                pf.NumberFormat = "$#,##0.00"
                'Debug.Print "Formatted " & pf.Name
            End Select
        End If

    Next

End Sub