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
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