Programmatically manage pivot table.

[Rich]

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]

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.

 

[Guest]

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:=xlDatabase, 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