Consulting

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...
    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 
    
    
    Formatting tags added by mark007
    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

  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
    For Each pf In pt.PivotFields 
    
    
    Formatting tags added by mark007
    to
    For Each pf In pt.DataFields 
    
    
    Formatting tags added by mark007
    Will still appreciate any alternatives or better practices...

    Justin

Posting Permissions

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