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
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