Pivot table sum data
 

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

Pivot table sum data
 

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


-----Original Message-----
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
.