Results 1 to 2 of 2

Thread: Change Pivot Field function from xlCount to xlSum

  1. #1

    Change Pivot Field function from xlCount to xlSum

    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...
    [vba]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[/vba]

    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?


  2. #2
    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
    [VBA]For Each pf in pt.PivotFields[/VBA]
    [VBA]For Each pf in pt.DataFields[/VBA]

    Will still appreciate any alternatives or better practices...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts