Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically manage pivot table.
I am using Excel 97.
I have a spreadsheet with the names of clients in column A and their amounts in column B. I recorded a pivot table where the client names are long the side and the sum of column b is in the pivot table. For whatever reason, the pivot table will sometimes count and not sum the values in column B. I wanted to know if it is possible to tell the pivot table by code to sum the values. Or is there a better way to make sure that the macro sums the values in column b and not count the values ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically manage pivot table.
The pivottable looks for numeric entries in that field. If there are any text
cells (or even empty cells), it changes it to "Count Of". Maybe you could record a macro when you create the pivottable and specify "Sum of". Then just rerun your macro when you need to rebuild the pivottable. Rich wrote: I am using Excel 97. I have a spreadsheet with the names of clients in column A and their amounts in column B. I recorded a pivot table where the client names are long the side and the sum of column b is in the pivot table. For whatever reason, the pivot table will sometimes count and not sum the values in column B. I wanted to know if it is possible to tell the pivot table by code to sum the values. Or is there a better way to make sure that the macro sums the values in column b and not count the values ? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically manage pivot table.
Rich-
I don't know if you are still looking for help, but here is some code to programtically create a pivot table on a new worksheet based on two columns of data that will automatically calculate the "Sum of.." the data in the second column. I have chosen to name the table "ClientTable". This sub assumes that the data appears in columns A and B, that the column headings (Field Names) are in row 1 and that there are no blank rows in the middle of the data set. Sub makePivot() Dim strHeading1 As String Dim strHeading2 As String strHeading1 = Range("A1").Value 'assign value found in cell A1 as Heading1 strHeading2 = Range("B1").Value 'assign value found in cell B1 as Heading2 Range("A1").CurrentRegion.Select 'select data set ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Selection).CreatePivotTable TableDestination:="", TableName:= _ "ClientTable" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("ClientTable").PivotFields (strHeading1) .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ClientTable").PivotFields (strHeading2) .Orientation = xlDataField .Position = 1 .Function = xlSum End With End Sub Stan Shoemaker Palo Alto, CA "Rich" wrote: I am using Excel 97. I have a spreadsheet with the names of clients in column A and their amounts in column B. I recorded a pivot table where the client names are long the side and the sum of column b is in the pivot table. For whatever reason, the pivot table will sometimes count and not sum the values in column B. I wanted to know if it is possible to tell the pivot table by code to sum the values. Or is there a better way to make sure that the macro sums the values in column b and not count the values ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Using Check Box to manage Pivot Table PLS!! | Excel Discussion (Misc queries) | |||
Pivot table: how to programmatically format headers? | Excel Discussion (Misc queries) | |||
Changing Pivot Table Fields Programmatically | Excel Programming | |||
ChangingPivot Table programmatically | Excel Programming |