I want pivot table to count instead of sum

[LonnieJohnson]

I have a module that creates a pivot table. I am new to VBA in EXCEL. I am an ACCESS, SQL Server programmer. My code works, but it sums my data field and I want it to count it instead. Can I override this in my code?

Thanks.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Chattin]

This is what the macro recorder spat out - I double clicked on the 'Sum of Amount' field in the pivot table to bro#ing up the pivottable field options and selected 'count' so perhaps you could work it into your macro

Sub Macro4()
Range("G1&quot.Select
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Sum of Amount&quot.Function = _
xlCount
End Sub

HTH

 

[bronc]

that's right the pivotfield object has the Function property taking the xlCount value to make it count. You need to set up your pivot table object variables first
Stephen Roman has an excelent chapter(20) on pivot tables in Writing Excel Macros.