Pivot table code

[kualjo]

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?

[mbarron]

Check your data for text values contained in the column that are being Summed/Counted. Counting will typically only happen if there is text, even if it's only one value in a column of thousands of records

[kualjo]

You pointed me in the right direction. My data table will always have 5 columns, but the number of rows will vary everytime the data is pulled. Because of this, I told the code to just use the entire worksheet, thus the Count instead of Sum. Now I have the problem of getting the code to use only the cells in which there is data. I haven't had time to dive into this yet, but if you've got some pointers, I'd appreciate it.
Thanks!

[mbarron]

You can use the current region such as:
Range("A1").CurrentRegion.Select