CODEWITHSUNDEEP
excelvba
coolkidlovestokodeeee
coolkidlovestokodeeee

Reputation: 11

Excel Pivot Table, VBA, change all Field Values to Count Numbers

I have this VBA marco which changes all the field values to Product. 

  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlProduct
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
    End Sub

When when I try changing xlProduct to xlCount.Numbers, the code doesn't work. Any idea how to fix this?

Upvotes: 0

Views: 786

Answers (2)

xtaycie
xtaycie

Reputation: 1

try this if you haven't already figured this out ...

With ActiveSheet.PivotTables("PivotTable1")
    .SmallGrid = False
     With .PivotFields("fieldname")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlCount
        .NumberFormat = "#"
     End With
 End With

Upvotes: 0

coolkidlovestokodeeee
coolkidlovestokodeeee

Reputation: 11

xlCountNums is the proper function.

https://www.thespreadsheetguru.com/blog/2014/8/12/quickly-change-pivot-table-field-calculation-from-count-to-sum

Upvotes: 1

Related Questions