Remove all the data fields and add new one in a Pivot Table

If you want to remove all the data fields from a pivot table then adding a new one. Snapshot below -

Here is the code-


Sub remove_data_fields()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim pt As PivotTable
Dim pf As PivotField
Dim pfNew As PivotField
Dim strSource As String
Dim strFormula As String


Set pt = Sheets(1).PivotTables("AKOUL")
' remove all the data fields
For Each pf In pt.DataFields
pf.Orientation = xlHidden

Next pf
' adding a new data field
pt.AddDataField pt.PivotFields(Sheets(1).Range("a1").Value), "Sum of " & Sheets(1).Range("a1").Value, xlSum

Set pt = Nothing
Set pf = Nothing


Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Download Excel File