Getting a Sum Instead of Count

Excel is smart. When you build a report with revenue, it assumes you want to sum the revenue. But, there is a problem. Say that one of the revenue cells is accidentally blank. When you build the pivot table, even though 99.9% of fields are numeric, Excel assumes you have alphanumeric data and offers to count this field. This is annoying. It seems to me to be an anomaly that on one hand, you are expected to make sure that 100% of your cells have numeric data, but the results of the pivot table are often filled with non-numeric blank cells.

When you build the pivot table in the Excel interface, you should take care in the Layout dialog to notice that the field says Count of Revenue instead of Sum of Revenue. At that point, the right thing is to go back and fix the data, but what people usually do is doubleclick the Count of Revenue button and change it to Sum of Revenue.

In VBA, you should always explicitly define that you are creating a sum of revenue by explicitly setting the Function property to xlSum:

' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 End With

At this point, we've given VBA all the settings required to correctly generate the pivot table. If you set the ManualUpdate to False, Excel calculates and draws the pivot table. You can immediately thereafter set this setting back to True:

' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True

At this point, you will have a complete pivot table like the one shown in Figure 12.5. Here is the complete code used to generate the pivot table:

Sub CreatePivot()

Dim WSD As Worksheet

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim PRange As Range

Dim FinalRow as Long

Set WSD = Worksheets("Pivot Table")

' Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

0 0

Post a comment