Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Pivot Table Source data as a Data Table | Excel Discussion (Misc queries) | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
Pivot Table - Use Other Pivot Table as Data Source | Excel Discussion (Misc queries) | |||
How do I sort pivot table data outside a pivot table | Excel Worksheet Functions |