VBA Code to Automatically Change Caption when Added to Pivot Table

[waverider80]

Hi everybody!

First off, I'd like to thank all of you for this incredible forum... it has helped me countless times in my work life!!

I have a simple pivot table with a few calculated fields. When I add the calculated fields to the pivot table, two issues occur:

1. The caption is renamed: Sum of [field]
2. The formatting reverts to a default

Instead, what I'd like to happen is if a user drags a pivot field from the PivotTable Field List onto the Pivot Table, then I'd like it to:

1. Automatically change the caption to a user friendly field name that I create (basically the name without "Sum of" and...
2. Change the formatting (i.e. a percentage would be 0.0%)

A couple things I've figured out... (please tell me if I'm wrong):

1. I'm going to place the code in the Sheet 1 Object of the VBA Project under the PivotTableChangeSync event so that it will check this code everytime a change is made to the pivot table. (Ideally, I'd like it to only check/run the macro if a field is added to the pivot table but can't find this event). Is this correct?
2. I'm going to use a For each Next loop to loop through all the pivot fields on the current pivot table
3. I'll use a case if statement to check which field it is and then if it's field A then change the caption and formatting to xx, and if field B then xx, etc. This part I've got down (by using the Macro Recorder)
4. Problem: I've put together the below code just to loop through the fields, but it seems to loop through all the fields that are listed in the PivotTable Field List rather than only the ones on the pivot table residing on the actual sheet. How can I loop through just the ones on the sheet?

Sub UpatePivotFieldNames()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.PivotFields
MsgBox pf.Caption
Next pf


End Sub

How can I get this code to only run through the fields on the pivot table in the sheet itself and not the FieldList? Also is there an event for when someone adds a field to the pivot table?

FYI: Using Excel 2010, Windows XP (work computer)

THANK YOU!!!! You guys are awesome!

 

[waverider80]

After browsing a little more, I found part of the answer to my own question and wanted to post for other readers' benefit!

To loop through the fields on the sheet and not PivotTable Field List I just need to use the DataFields object rather than the PivotFields object so that the code becomes (change in red):

Sub UpatePivotFieldNames()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.DataFields
MsgBox pf.Caption
Next pf

Nice!

If anyone knows if there is an event for when a user drags a field onto the pivot table, please let me know!

 

[waverider80]

I've completed my code, and it works! I wanted to submit it here for those who find it helpful and also for critiquing if anyone is willing to suggest any improvements.

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.DataFields
Select Case pf.Caption
Case "Sum of Metric1"
pf.Caption = "Metric1 "
pf.NumberFormat = "#,##0.0"
Case "Sum of Metric2"
pf.Caption = "Metric2 "
pf.NumberFormat = "0.0%"
Case "Sum of Metric3"
pf.Caption = "Metric3 "
pf.NumberFormat = "0.0%"
End Select
Next pf

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

 

[dswinsto]

I didn't know that Caption was what I need to change until I read this thread - thanks for the help and here's the code I used in case anyone might think it helpful for future reference:

'PIVOT TABLE
Dim objTable As PivotTable
Dim objField As PivotField






    ActiveWorkbook.Sheets("Sup_Data(2)").Select
    Range("A1").Select
    Set objTable = Sheets("Sup_Data(2)").PivotTableWizard


    ActiveWindow.DisplayGridlines = False
    ActiveSheet.Name = "Supervised Pivot"
'
    Set objField = objTable.pivotfields("Metric")
    objField.Orientation = xlRowField






    
    


    Set objField = objTable.pivotfields("Q1 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
     objField.NumberFormat = "#,##0_);[Red](#,##0)"
     objField.Caption = "Q1_FY13"
 
'
     Set objField = objTable.pivotfields("Q2 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
     objField.NumberFormat = "#,##0_);[Red](#,##0)"
     objField.Caption = "Q2_FY13"


     Set objField = objTable.pivotfields("Q3 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q3_FY13"


     Set objField = objTable.pivotfields("Q4 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q4_FY13"


     Set objField = objTable.pivotfields("Q1 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q1_FY14"


    Set objField = objTable.pivotfields("Q2 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q2_FY14"
 


    Set objField = objTable.pivotfields("Q3 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q3_FY14"


    Set objField = objTable.pivotfields("Q4 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q4_FY14"


'Moves Values to Columns in pivot
 Set objField = objTable.pivotfields("Data")
objField.Orientation = xlColumnField

I would love to make the pivotfields an array but I'm not really sure how to do that here

Thanks!