How to replace pivot table data fields using VBA?
I'm progamming a pivot table using VBA. I have no problem manipulating
column or row fields, but how can I replace a data field? Everything I try just adds the data field to the existing data field resulting in a mess.
The PivotTable.addfields methods *replaces* whatever columns were previously designated as RowFields, ColumnFields, and PageFields with the new specifications, as in:
.AddFields RowFields:="Date", PageFields:="Location"
But no matter what I try, I can't replace the data field.... I've even tried setting the Orientation property to xlHidden, as follows:
' Hide existing data fields
For each pf In .DataFields
Debug.Print "deleting pf", pf.Name
pf.Orientation = xlHidden
'pf.Delete ' I tried deleting too
They key is that I need to do this via code. I can easily do it by using the Excel GUI. I recorded the resulting code - Excel was hidding the field by setting its Orientation property to xlHidden. Why doesn't that work for me?
Many thanks in advance,