Can I use a Wild Character in this Code?

Tim_D

New Member
Joined
Apr 23, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Tim_D

New Member
Joined
Apr 23, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can Anyone help with this?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,747
Messages
5,597,896
Members
414,186
Latest member
truebuddy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top