Search code examples
excelvbapivot-tableexcel-pivot

Can I get the name of a single Field in the "Values" quadrant of PivotTable?

Preface:

I am trying to build a macro that relies on no absolute references.

Problem:

Part of my macro needs the name of the field in the "Values" quadrant of the active PivotTable.

enter image description here

The field name will change so I cannot hard code it into my macro. (it is "AMT" in the image above, but it might be "Amount" in another PivotTable, which is why I need it to be dynamic.)

If I have a single Field in the value quadrant, how can I get it's name as a string? If I can just get it's name as a string, I can feed that into the macro that I have.

Speaking of which, here is my code just for reference:

Sub PivotNumberFormat()   
'PivotNumberFormat Macro
'Formats the "Values" data field in the currently active pivot to: number format, 2 decimals, and commas
'=============================================
'Define variables
Dim PT As PivotTable

'=============================================
'Continue if pivot table not selected
On Error GoTo EndSub

'=============================================
'Set pivot table as variable "PT"
Set PT = ActiveCell.PivotTable

'=============================================
'If pivot table selected, change number format
If Not PT Is Nothing Then

    'Change the format of the pivot table amt field
    With PT.PivotFields("Sum of AMT")  '<-------------This is the line where I need a dynamic reference.
        .NumberFormat = "#,##0.00_);(#,##0.00)"
    End With
    
    'Notify user that format was changed
    MsgBox "Format Changed"
    
    'Stop macro
    Exit Sub
    
End If

'=============================================
'If error, notify user and stop macro
EndSub:

    MsgBox "Format NOT Changed"
    
End Sub

You can see about halfway down I put a note showing the PivotFields object that needs a dynamically-referenced name. Instead of the code reading "(Sum of AMT)", I want it to read "(Sum of [FieldName])" with [FieldName] being the dynamic field name string that changes with the PivotTable.

The vast majority of the time that I will use this macro, there will only be one field in the Values quadrant. If it is easy to make the macro scalable to multiple fields in the Values quadrant, that is a bonus, but definitely not required or needed.

I know that PivotTable vba is complex so I appreciate any of your valuable insight you bring to the table!

Thanks, Logan

Solution

  • This might get you started:

    Dim f As PivotField
    
For Each f In ActiveSheet.PivotTables(1).DataFields
    Debug.Print f.SourceName, f.Name
Next f

    See also: https://www.contextures.com/excel-vba-pivot-table-field-list.html