Pivot table sum data

  • Thread starter Thread starter Fish
  • Start date Start date
F

Fish

Group,

I coded a pivot table into my macro and it works
sometimes; however, sometimes the data in the pivot table
works under the field settings as a count rather then a
sum. How do i make my macro change the data to a sum if it
is a count function and not give me an error . Thanks in
advance.Code for pivot down below

Regards,

Fish

'Macro

Dim fname As String
Dim PTcache As PivotCache
Dim PT As PivotTable

fname = ActiveWorkbook.Name



Range("a1").Select

'Application.DisplayAlerts = False

Set PTcache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("a1").CurrentRegion.Address)

Set PT = PTcache.CreatePivotTable _
(Tabledestination:="", _
Tablename:="Pivottable1")

With PT
.PivotFields("LOCATION").Orientation = xlPageField
.PivotFields("CAT TYPES").Orientation = xlColumnField
.PivotFields("DATE").Orientation = xlRowField
.PivotFields("Cat Color").Orientation = xlRowField
.PivotFields("DATE").LayoutSubtotalLocation = xlAtTop
.PivotFields("DATE").LayoutForm = xlOutline
.PivotFields("COST OF CAT").Orientation = xlDataField

End With
ActiveSheet.PivotTables(PT).PivotFields("Count of
Cost of cat").Function = xlSum

ActiveSheet.PivotTables(PT).ShowPages
PageField:="Location"

End sub
 
Hi Fish

Try this:

For Each pvtfld In ActiveSheet.PivotTables(1).DataFields
pvtfld.Function = xlSum
Next

Doesn't make a difference if the datafield is sum or
count. If it is already sum then nothing happens. If the
field is count then it will be changed to sum.

Mike
 
Back
Top