Can I use a Wild Character in this Code?

[Tim_D]

Hi All,

One of my biggest grips about Excel Pivots is the default number format of "general" for the values field. I cannot change the default (or anything else with default pivot layouts), because Pivot Tables Options is not available in my work copy of Office 2016.

I am not a VBA coder, but I can figure out/understand and modify code I find using Google when I have an automation or time-saving need to edit/format data. I have found some code from different sources and cobbled it together to change the number format with the click of an macro button added to my ribbon... But, it is limited because I have to change the name of the column that I want sum in order for the code to execute. This is not ideal. I would like it to simply change the number format regardless of what the column is called. I thought maybe adding a wild character would work since "Sum of" is always present. I tried "Sum of*" in the code but it does not work.

Any Suggestions?

Kind Regards, Tim

Sub ChangeNumberFormat()
'PURPOSE: Change Number Format

Dim pvt As PivotTable
Dim pf As String
Dim pf_Name As String

Set pvt = ActiveSheet.PivotTables(1)

'Name Values Field
    pf = "Sum of Translation Amount"
    pf_Name = "Sum of Translation Amount"
   
'Change Pivot Field Number Format
    Range("B4").Select
    With pvt.PivotFields("Sum of Translation Amount")
        .numberformat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

 

[Tim_D]

Can Anyone help with this?