Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Problem
I am using the following code to create a pivot table which I want to return the sum function for "Balance". For some reason, the pivot table started returning Count of Balance rather than Sum of Balance so I added the line ..PivotFields("Count of Balance").Function=xlSum. This changed the count to sum. Now the pivot table is once again returning Sum of Balance initially and the code hangs when it trys to convert the count to sum. Is there some code I can use to cause the function to default to sum each time the pivot table runs? Range(FirstCell, LastCell).Select CurrentRegion = Selection Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlDatabase, _ SourceData:=Range("A1").CurrentRegion.Address) 'Balance by AssignedTo Set PT = PTCache.CreatePivotTable _ (TableDestination:="'AssignedTo'!R3C1", _ TableName:="PivotTable1") With PT .PivotFields("AssignedTo").Orientation = xlRowField .PivotFields("Balance").Orientation = xlDataField .PivotFields("Count of Balance").Function = xlSum End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Problem
You could change your code slightly:
'=========================== With PT.PivotFields("Balance") .Orientation = xlDataField .Caption = "Balance " .Function = xlSum End With '============================ Boyd L. Colglazier wrote: I am using the following code to create a pivot table which I want to return the sum function for "Balance". For some reason, the pivot table started returning Count of Balance rather than Sum of Balance so I added the line .PivotFields("Count of Balance").Function=xlSum. This changed the count to sum. Now the pivot table is once again returning Sum of Balance initially and the code hangs when it trys to convert the count to sum. Is there some code I can use to cause the function to default to sum each time the pivot table runs? Range(FirstCell, LastCell).Select CurrentRegion = Selection Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlDatabase, _ SourceData:=Range("A1").CurrentRegion.Address) 'Balance by AssignedTo Set PT = PTCache.CreatePivotTable _ (TableDestination:="'AssignedTo'!R3C1", _ TableName:="PivotTable1") With PT .PivotFields("AssignedTo").Orientation = xlRowField .PivotFields("Balance").Orientation = xlDataField .PivotFields("Count of Balance").Function = xlSum End With -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
Pivot Table Problem | Excel Worksheet Functions | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Pivot table problem | Excel Discussion (Misc queries) | |||
Pivot Table problem | Excel Discussion (Misc queries) |