Pivot Value Number Formatting

[xcelljunkie]

Hi All,

This has been a peeve of mine for years so I finally decided to do something about it. I use pivot tables all the time and have to manually change the formatting of each field because I usually have both units and revenue in the same pivot. (Each needs a different format)
I've built the following to change the formatting of the selected data field, but you have to select the data field name, and I'd like to be able to select any value in the field run it. Any ideas of how i can get the field name by selecting any value within the field?

Thanks!

Public Sub SetRevenueDataField()


Dim ptf As PivotField
Dim name As String
fldname = ActiveCell.Value
With Selection.PivotTable
.ManualUpdate = True
With .DataFields(fldname)
.NumberFormat = "$#,##0"
End With
.ManualUpdate = False
End With
End Sub

Public Sub SetUnitDataField()
Dim ptf As PivotField
Dim name As String
fldname = ActiveCell.Value
With Selection.PivotTable
.ManualUpdate = True
With .DataFields(fldname)
.NumberFormat = "#,##0"
End With
.ManualUpdate = False
End With
End Sub