Sunday, October 9, 2011

Add Multiple Data Fields To Pivot Table

If you have created a pivot table already in your worksheet. Now if you want to add multiple data fields to it. Snapshot-


Here is the code-

Sub add_multiple_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

' make sure the pivot data source should include all the data field columns in it

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

For i = 3 To 8
pt.AddDataField pt.PivotFields(Sheets(2).Cells(1, i).Value), "Sum of " & Sheets(2).Cells(1, i).Value, xlSum
Next i
Set pt = Nothing
Set pf = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Download Excel Macro File

No comments:

Post a Comment