VBA: Use macro to automatically format value field settings of pivot table to a single value

[nflami]

Hello all,

Currently I have the below code which is successful at formatting all the value field settings in the selected pivot table to my specified format. My goal is to be able to apply the same macro, but to the single selected value field, rather than all the value fields.

Thanks for your help!

Public Sub SetDataFieldsToSum()

' Nick

   Dim ptf               As PivotField
   With Selection.PivotTable
      .ManualUpdate = True
      For Each ptf In .DataFields
         With ptf
            .Function = xlSum
            .NumberFormat = "#,##0_);[Red](#,##0)"
         End With
      Next ptf
      .ManualUpdate = False
   End With
End Sub

 

[Jerry Sullivan]

Hi nflami,

If you know the name of the data field, you can reference it directly like this...

   Dim ptf  As PivotField

   With Selection.PivotTable
      .ManualUpdate = True
      With .DataFields("Sum of Sales")
         .Function = xlSum
         .NumberFormat = "#,##0_);[Red](#,##0)"
      End With
      .ManualUpdate = False
   End With

 

[nflami]

Hi Jerry,

Thank you! This is awesome and will be very helpful. I would like to take this a step further if possible. Is it possible to reference the data field by the current cursor location. So if I was in the cell titled "Sum of Sales" it would then execute the macro to the data field? That way I could used this across multiple fields based on the format I want.

Thanks again,
Nick

 

[nflami]

I don't really understand how to write macros with a Dim so the below code doesn't work, but may this will help you to see what I'm hoping for. Based on the text in the active cell, I want apply that to the data field.

Public Sub SetSingleDataFieldsToSum()

   Dim ptf  As PivotField
   Dim name  As String
   name = ActiveCell.Value
   

   With Selection.PivotTable
      .ManualUpdate = True
      With .DataFields("String")
         .Function = xlSum
         .NumberFormat = "#,##0_);[Red](#,##0)"
      End With
      .ManualUpdate = False
   End With

End Sub

Thanks!

 

[Jerry Sullivan]

You were close. It would work if you changed it to:

With .DataFields(name)

I'd suggest a couple more modifications:

It's best to avoid using keywords like "name" that have other meanings in VBA as variable names.

If you reference ActiveCell.PivotField.Name then you can run this macro after having selected any DataValue in that field- not just the heading.

Here's a modified version...

Public Sub SetSingleDataFieldsToSum()

   Dim ptf  As PivotField
   Dim sFieldName  As String
   
   sFieldName = ActiveCell.PivotField.Name
   
   With Selection.PivotTable
      .ManualUpdate = True
      With .DataFields(sFieldName)
         .Function = xlSum
         .NumberFormat = "#,##0_);[Red](#,##0)"
      End With
      .ManualUpdate = False
   End With

End Sub

 

[nflami]

[SOLVED] Re: VBA: Use macro to automatically format value field settings of pivot table to a single value

Thank you, exactly what I was looking for. I appreciate your help and the pointers.