VBA (or other method) for moving field in and out of values area

[Bacon117]

I need to quickly change which field is listed in the Values area of a pivot table. My current method is to drag the field out of the values area, drag the new field into the values area, right click it, and select average.

I would really like to write a VBA script to handle this for me based on a value in a cell (that has a drop down list populated from the header of my data). BUT, I cannot seem to find a way to determine WHICH field is currently in the values area before deleting it from the pivot table. All of the tutorials (and microsoft reference) show how to remove a field from a pivot chart, but only if you know which field is already there. I suppose I could remove everything, then add it all back, but I think that's a rabbit hold I'm not prepared for.

Any suggestions?

Thanks.

 

[Bacon117]

Maybe an easier question: When I add a field into a pivot table with VBA, how do I specify which area that field should go to?

 

[Bacon117]

Bump!