Pivot Table - setting sum versus count

[jaks999]

I have the following procedure for creating a pivot table. Normally it works fine and produces a "Sum of balance" in the data area.

However, every now and then, on data in a new spreadsheet, it does a "Count" of the data instead of the "Sum"

Why does it do this and how can I ensure that it only does a Sum?

I would appreciate help on this.

Regards, JAKS999

Sub CreatePivotTable()
Dim PTcache As PivotCache
Dim PT As PivotTable
Range("A5").Select


On Error Resume Next
Sheets("Sheet1").DrawingObjects("TextBoxWait").Visible = True
On Error GoTo 0
Application.ScreenUpdating = False

' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

' Create a Pivot Cache
Set PTcache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A5").CurrentRegion.Address)

' Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"

' Create the Pivot Table from the Cache
Set PT = PTcache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="BudgetPivot")

With PT
' Add fields
.PivotFields("Year").Orientation = xlColumnField
.PivotFields("Activity").Orientation = xlRowField
.PivotFields("Type").Orientation = xlRowField
.PivotFields("Balance").Orientation = xlDataField
End With

ActiveSheet.PivotTables("BudgetPivot"). _
PivotFields("Sum of Balance").Function = xlSum

Columns("C:E").Select
Selection.Style = "Comma"
Columns("C:E").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit

Application.ScreenUpdating = True
On Error Resume Next
Sheets("Sheet1").DrawingObjects("TextBoxWait").Visible = False

End Sub

 

[SkipVought]

Hi,

However, every now and then, on data in a new spreadsheet, it does a "Count" of the data instead of the "Sum"

If the field in the DATA AREA is not numeric, it can ONLY COUNT occurrences.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[KenWright]

It may also default to Count if you have blanks in your data, so what you could do is select all your data first, do edit / go To / Special / Blanks, type 0 and then hit CTRL+ENTER to poulate all the blanks with a 0 if that was appropriate.

Only caveat with SpecialCells is that if you have more than 8192 Non-Contiguous ranges then it will only return one range which is your entire selection, in which case do it in more than one hit.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jaks999]

to Skip & Ken,

Thanks for the comments - that helped me sort my problem out.

Cheers,