Pivot Table Permanent Formats

[BoothHopkins]

Hi,

I have a pivot table I am using for a report. I would like to format several variables differently (ie spend would be in accounting format, sales as general, etc). I know you can change the format by right clicking on the variable, but I would like a more permanent solution so that if I remove the variable from the "Values" list, it will still have the designated format when I bring it back in.

Thanks!

[JosephP]

the formats are not saved with the fields. you can use code if you have specific formats for specific fields

[BoothHopkins]

Yes, I need specific formats for each variables. How would I code this?

Thanks!

[JosephP]

for instance

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pf As PivotField
    Application.EnableEvents = False
    For Each pf In Target.DataFields
        Select Case LCase$(pf.SourceName)
            Case "value"
                pf.NumberFormat = "#,##0.00"
            Case "id"
                pf.NumberFormat = "General"
        End Select
    Next pf
    Application.EnableEvents = True
End Sub

this code must be placed in the worksheet module for the sheet containing the pivot table (right-click the tab and choose view code and then paste it in)