kualjo
03-05-2010, 12:03 PM
I recorded some code to create a pivot table and it works just fine, but with one unusual problem. As I tested and refined it, the pivot table would sometimes create a "Sum of" table and other times a "Count of" table. It seems to be arbitrary. First question: Why does it do this? Second: How can I make it default to "Sum of" every time? Here is the code I currently have:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"ZDP04!R1C1:R10000C5").CreatePivotTable TableDestination:="", TableName:= _
"ZDP04Pivot", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ZDP04Pivot").ColumnGrand = False
ActiveSheet.PivotTables("ZDP04Pivot").AddFields RowFields:=Array("SKU", _
"Plant"), ColumnFields:="Week"
ActiveSheet.PivotTables("ZDP04Pivot").PivotFields("Manag. demand"). _
Orientation = xlDataField
Any ideas?
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"ZDP04!R1C1:R10000C5").CreatePivotTable TableDestination:="", TableName:= _
"ZDP04Pivot", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ZDP04Pivot").ColumnGrand = False
ActiveSheet.PivotTables("ZDP04Pivot").AddFields RowFields:=Array("SKU", _
"Plant"), ColumnFields:="Week"
ActiveSheet.PivotTables("ZDP04Pivot").PivotFields("Manag. demand"). _
Orientation = xlDataField
Any ideas?