Pivot Table Question

[terryspencer2003]

I have a pivot table. Instead of putting more than one data type into
the table I want to use VBA to insert a new data type within a loop.
The array for the pivot table has all the data types that I need.
That is instead of dragging the new Data field item from the right
into the Data area, I want VBA to insert the new one for me.

I recorded the following code:

ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of
A").Orientation _
= xlHidden
With ActiveSheet.PivotTables("PivotTable5").PivotFields("B")
.Orientation = xlDataField
.Caption = "Average of B"
.Function = xlAverage
End With
ActiveSheet.PivotTables("PivotTable5").DataPivotField.PivotItems(
_
"Average of Soderland").Position = 1
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False

Here you can see that it replaced the field item "A" with "B". Then
it assigned an "Average" to it. I need this to be to fancy at this
point. I simply want to be able to replace the "B" with a different
variable. I can even type this in manually at this point. The
problem is, that the macro starts of with a reference to "Count of A".
How do I set this up so that I can simply replace "B" with a new
variable and have it work?

TS

 

[Bill Manville]

wrote:

The
problem is, that the macro starts of with a reference to "Count of A".
How do I set this up so that I can simply replace "B" with a new
variable and have it work?

Try replacing

.PivotFields("Count of A")
with
.DataFields(1)
assuming you are only displaying one datafield

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup