PDA

View Full Version : Change Pivot Field function from xlCount to xlSum



jdub12280
03-27-2010, 11:11 AM
Back again...

I have a workbook with several pivots, all using the same source data. When i update the pivots to include new data, they pivot field defaults to using the xlcount function... and i usually have to go in and check each pivot to make sure they are all changed to xlsum. I have been using the following colde to update the source for each pivot in the workbook...and have toyed with adding the lines to change the function to xlsum...
For Each ws In ActiveWorkbook.Worksheets

For Each pt In ActiveWorkbook.Worksheets(ws.Name).PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=src)

For Each pf In pt.PivotFields
If Not pf Is Nothing Then
With pf
.Function = xlSum
End With
End If
Next pf

Next pt

Next ws

I get a run timer error '1004':
unable to set the Function property of the PivotField class


The Excel Help offered no guidance...

Any ideas?

Justin

jdub12280
03-27-2010, 11:20 AM
I think i have stumbled upon my solution... not sure what consequences there may be, but it seems to be working.

I changed the line
For Each pf in pt.PivotFields
to
For Each pf in pt.DataFields

Will still appreciate any alternatives or better practices...

Justin