Microsoft Office Tutorials and References
In Depth Information
Manipulating PivotFields in VBA
MAnipulATing piVoTfiElds in VBA
PivotFields are the row and column areas that you place your field names into, depending on how
you want the PivotTable to display your data. The following pieces of VBA code perform the
placement of PivotFields as they were for the PivotTable that was manually created earlier in the lesson.
Two fields (Region and Store ID) are placed as row labels, and one field (When) is placed as a
column label. The Revenue field is placed in the Values area, and the Report Filter area is populated by
the Items field.
With ActiveSheet.PivotTables(1)
‘First (outer) row field
With .PivotFields(“Region”)
.Orientation = xlRowField
.Position = 1
End With
‘Second (inner) row field
With .PivotFields(“Store ID”)
.Orientation = xlRowField
.Position = 2
End With
‘Column field.
With .PivotFields(“When”)
.Orientation = xlColumnField
.Position = 1
End With
‘Report Filter field
With .PivotFields(“Item”)
.Orientation = xlPageField
.Position = 1
End With
‘Revenue in the Values field
.AddDataField ActiveSheet.PivotTables(1).PivotFields(“Revenue”), _
“Sum of Amount”, xlSum
End With
Be sure to name your PivotFields correctly! They must be spelled the same way
in your code as they are in the header cells of your source list. If you misspell the
field names in your code, VBA will let you know with a run time error, because
the field names you’d be instructing VBA to manipulate do not exist.
Search JabSto ::

Custom Search