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 ?

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

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 ?