Add/Remove pivot fields via macro- Help req to change existing code

[aravindhan_3]

Hi,

Hope you are good today! I need help for one of problem I am facing.

I wanted to Keep buttons to add fields to Rows, Buttons to add Columns & buttons to add value fields in pivot. the codes in the attached file works fine. When button is clicked once, it adds and removes when it click again. This works well for Rows and column fields, but not working for Value fields, it keeps adding same field again n again, as pivot tables are meant for that when it comes for values.

I am sure there would be a work around for that. Please help. attached the reference file as well.


Sub Toggle_value_Field()'Add/Remove the field to the pivot table.
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim shp As Shape

'Set variables
Set pt = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text

'Toggle field
'If visible then hide it
If pt.PivotFields(sField).Orientation = xlDataField Then
pt.PivotFields(sField).Orientation = xlHidden
shp.Fill.ForeColor.Brightness = 0.5
Else 'Add to Rows area
pt.PivotFields(sField).Orientation = xlDataField
shp.Fill.ForeColor.Brightness = 0
End If
End Sub


got this code from



Author:
Jon Acampora





Source:
https://www.excelcampus.com/vba/macro-buttons-pivot-table-fields/


















Free Newsletter:
http://www.excelcampus.com/newsletter

[p45cal]

See attached

[aravindhan_3]

hello P45,

Thanks for the quick help. This one worked well & thank you very much for that. Just checking is there a way to add
1. Count of Revenue
2. Add calculated fields

Regards
Arvind



Regards
Arvind

[p45cal]

Count of Revenue as well as, or instead of, Sum of Revenue?

Re. calculated fields; it's very likely possible but we'll need to know exactly what you want. I recorded this:
ActiveSheet.PivotTables("PivotTable4").CalculatedFields.Add "zippee", "='Unit Price' *Quantity", True
ActiveSheet.PivotTables("PivotTable4").PivotFields("zippee").Orientation = xlDataField