12. Using VBA to Create Pivot Tables

Introducing Pivot Tables

Pivot tables are the most powerful tools that Excel has to offer. The concept was first put into practice by Lotus with its Improv product.

I love pivot tables because they are a fast way to summarize massive amounts of data. The name pivot table comes from the ability you have to drag fields in the drop zones and have them recalculate. You can use the basic vanilla pivot table to produce a concise summary in seconds. However, pivot tables come in so many flavors that they can be the tools of choice for many different uses. You can build pivot tables to act as the calculation engine to produce reports by store or by style, or to quickly find the top 5 or bottom 10 of anything.

I am not suggesting you use VBA to build pivot tables to give to your user. I am suggesting you use pivot tables as a means to an end—use a pivot table to extract a summary of data and then take this summary on to better uses.

Understanding Versions

As Microsoft invests in making Excel the premier choice in business intelligence, pivot tables continue to evolve. They were introduced in Excel 5 and perfected in Excel 97. In Excel 2000, pivot table creation in VBA was dramatically altered. Some new parameters were added in Excel 2002. A few new properties such as PivotFilters and TableStyle2 were added in Excel 2007. Slicers and new choices for Show Values As were added in Excel 2010. Timelines and the PowerPivot Data Model have been added in Excel 2013. Therefore, you need to be extremely careful when writing code in Excel 2013 that might be run in legacy versions.

Much of the code in this chapter is backward-compatible all the way to Excel 2000. Pivot table creation in Excel 97 required using the PivotTableWizard method. Although this book does not include code for Excel 97, one example has been included in the sample file for this chapter.

Each of the previous three versions of Excel offered many new features in pivot tables. If you use code for a new feature, the code works in the current version, but it crashes in previous versions of Excel.

• Excel 2013 introduced the PowerPivot Data Model. You can add tables to the Data Model, create a relationship, and produce a pivot table. This code does not run in Excel 2010 or earlier. The function xlDistinctCount is new. Timelines are new.

• Excel 2010 introduced slicers, Repeat All Item Labels, Named Sets, and several new calculation options: xlPercentOfParentColumn, xlPercentOfParentRow, xlPercentRunningTotal, xlRankAscending, and xlRankDescending. These do not work in Excel 2007.

• Excel 2007 introduced ConvertToFormulas, xlCompactRow layout, xlAtTop for the subtotal location, TableStyles, and SortUsingCustomLists. Macros that include this code fail in previous versions.

Building a Pivot Table in Excel VBA

This chapter does not mean to imply that you should use VBA to build pivot tables to give to your clients. Instead, the purpose of this chapter is to remind you that you can use pivot tables as a means to an end; you can use a pivot table to extract a summary of data and then use that summary elsewhere.

Defining the Pivot Cache

In this first part of this chapter, the dataset is an eight-column by 5,000-row dataset as shown in Figure 12.1. The macros create a regular pivot table from the worksheet data. Near the end of the chapter, an example shows how to build a pivot table based on the Data Model and PowerPivot.

Figure 12.1. Create summary reports from this dataset.

In Excel 2000 and later, you first build a pivot cache object to describe the input area of the data:

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")

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

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=PRange)

Creating and Configuring the Pivot Table

After defining the pivot cache, use the CreatePivotTable method to create a blank pivot table based on the defined pivot cache:

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, _
FinalCol + 2), TableName:="PivotTable1")

In the CreatePivotTable method, you specify the output location and optionally give the table a name. After running this line of code, you have a strange-looking blank pivot table, like the one shown in Figure 12.2. You now have to use code to drop fields onto the table.

Figure 12.2. When you use the CreatePivotTable method, Excel gives you a four-cell blank pivot table that is not very useful.

If you choose the Defer Layout Update setting in the user interface to build the pivot table, Excel does not recalculate the pivot table after you drop each field onto the table. By default in VBA, Excel calculates the pivot table as you execute each step of building the table. This could require the pivot table to be executed a half-dozen times before you get the final result. To speed up your code execution, you can temporarily turn off calculation of the pivot table by using the ManualUpdate property:

PT.ManualUpdate = True

You can now run through the steps needed to lay out the pivot table. In the .AddFields method, you can specify one or more fields that should be in the row, column, or filter area of the pivot table.

The RowFields parameter enables you to define fields that appear in the Rows drop zone of the PivotTable Field List. The ColumnFields parameter corresponds to the Columns drop zone. The PageFields parameter corresponds to the Filter drop zone.

The following line of code populates a pivot table with two fields in the row area and one field in the column area:

' Set up the row & column fields
PT.AddFields RowFields:=Array("Region", "Customer"), _
    ColumnFields:="Product"

To add a field such as Revenue to the values area of the table, you change the Orientation property of the field to be xlDataField.

Adding Fields to the Data Area

When you are adding fields to the Data area of the pivot table, there are many settings you should control instead of letting Excel’s IntelliSense decide.

For example, say you are building a report with revenue in which you will likely want to sum the revenue. If you don’t explicitly specify the calculation, Excel scans through the data in the underlying data. If 100 percent of the revenue columns are numeric, Excel sums those columns. If one cell is blank or contains text, Excel decides on that day to count the revenue, which produces confusing results.

Because of this possible variability, you should never use the DataFields argument in the AddFields method. Instead, change the property of the field to xlDataField. You can then specify the Function to be xlSum.

While you are setting up the data field, you can change several other properties within the same With...End With block.

The Position property is useful when you are adding multiple fields to the data area. Specify 1 for the first field, 2 for the second field, and so on.

By default, Excel renames a Revenue field to have a strange name like Sum of Revenue. You can use the .Name property to change that heading back to something normal.

You are not required to specify a number format, but it can make the resulting pivot table easier to understand, and takes only one extra line of code:

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

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

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

Your pivot table inherits the table style settings selected as the default on whatever computer happens to run the code. If you want control over the final format, you can explicitly choose a table style. The following code applies banded rows and a medium table style:

' Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"

If you want to reuse the data from the pivot table, turn off the grand totals and subtotals and fill in the labels along the left column. The fastest way to suppress the 11 possible subtotals is to turn Subtotals(1) to True and then to False:

With PT
    .ColumnGrand = False
    .RowGrand = False
    .RepeatAllLabels xlRepeatLabels ' New in Excel 2010
End With
PT.PivotFields("Region").Subtotals(1) = True
PT.PivotFields("Region").Subtotals(1) = False

At this point, you have a complete pivot table like the one shown in Figure 12.3.

Figure 12.3. Fewer than 50 lines of code created this pivot table in less than a second.

Listing 12.1 shows the complete code used to generate the pivot table.

Listing 12.1. Code to Generate a 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("PivotTable")

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

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Region", "Customer"), _
        ColumnFields:="Product"

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

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

    'Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .RepeatAllLabels xlRepeatLabels ' New in Excel 2010
    End With
    PT.PivotFields("Region").Subtotals(1) = True
    PT.PivotFields("Region").Subtotals(1) = False
    WSD.Activate
    Range("J2").Select

End Sub

---

Learning Why You Cannot Move or Change Part of a Pivot Report

Although pivot tables are incredible, they have annoying limitations; for example, you cannot move or change just part of a pivot table. Try to run a macro that clears row 2. The macro comes to a screeching halt with the error 1004, as shown in Figure 12.4. To get around this limitation, you can copy the pivot table and paste as values.

Figure 12.4. You cannot delete just part of a pivot table.

Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values

Knowing the size of a pivot table in advance is difficult. If you run a report of transactional data on one day, you might or might not have sales from the West region, for example. This could cause your table to be either six or seven columns wide. Therefore, you should use the special property TableRange2 to refer to the entire resultant pivot table.

PT.TableRange2 includes the entire pivot table. In Figure 12.5, TableRange2 includes the extra row at the top with the field heading of Revenue. To eliminate that row, the code copies PT.TableRange2 but offsets this selection by one row by using .Offset(1, 0). Depending on the nature of your pivot table, you might need to use an offset of two or more rows to get rid of extraneous information at the top of the pivot table.

Figure 12.5. This figure shows an intermediate result of the macro. Only the summary in J12:M17 will remain after the macro finishes.

The code copies PT.TableRange2 and uses PasteSpecial on a cell five rows below the current pivot table. At that point in the code, your worksheet appears as shown in Figure 12.5. The table in J2 is a live pivot table, and the table in J12 is just the copied results.

You can then eliminate the pivot table by applying the Clear method to the entire table. If your code is then going on to do additional formatting, you should remove the pivot cache from memory by setting PTCache equal to Nothing.

The code in Listing 12.2 uses a pivot table to produce a summary from the underlying data. At the end of the code, the pivot table is copied to static values and the pivot table is cleared.

Listing 12.2. Code to Produce a Static Summary from a Pivot Table

---

Sub CreateSummaryReportUsingPivot()
    ' Use a Pivot Table to create a static summary report
    ' with product going down the rows and regions across
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Set WSD = Worksheets("PivotTable")

    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    WSD.Range("J1:Z1").EntireColumn.Clear

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row fields
    PT.AddFields RowFields:="Product", ColumnFields:="Region"

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

    With PT
        .ColumnGrand = False
        .RowGrand = False
        .NullString = "0"
    End With

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

    ' PT.TableRange2 contains the results. Move these to J12
    ' as just values and not a real pivot table.
    PT.TableRange2.Offset(1, 0).Copy
    WSD.Cells(5 + PT.TableRange2.Rows.Count, FinalCol + 2). _
        PasteSpecial xlPasteValues

    ' At this point, the worksheet looks like Figure 12.5
    ' Stop

    ' Delete the original Pivot Table & the Pivot Cache
    PT.TableRange2.Clear
    Set PTCache = Nothing

    WSD.Activate
    Range("J12").Select
End Sub

---

The code in Listing 12.2 creates the pivot table. It then copies the results and pastes them as values in J12:M13. Figure 12.5, which was shown previously, includes an intermediate result just before the original pivot table is cleared.

So far, this chapter has walked you through building the simplest of pivot table reports. Pivot tables offer far more flexibility. The sections that follow present more complex reporting examples.

Using Advanced Pivot Table Features

In this section, you take the detailed transactional data and produce a series of reports for each product line manager. This section covers the following advanced pivot table steps that are required in these reports:

1. Group the daily dates up to yearly dates.

2. Add multiple fields to the value area.

3. Control the sort order so the largest customers are listed first.

4. Use the ShowPages feature to replicate the report for each product line manager.

5. After producing the pivot tables, convert the pivot table to values and do some basic formatting.

Figure 12.6 shows the report for one product line manager so that you can understand the final goal.

Figure 12.6. Using pivot tables simplifies the creation of the report.

Using Multiple Value Fields

The report has three fields in the values area: Count of Orders, Revenue, and % of Total Revenue. Anytime you have two or more fields in the values area, a new virtual field named Data becomes available in your pivot table.

In Excel 2013, this field appears as sigma values in the drop zone of the Pivot Table Field List. When creating your pivot table, you can specify Data as one of the column fields or row fields.

The position of the Data field is important. It usually works best as the innermost column field.

When you define your pivot table in VBA, you have two columns fields: the Date field and the Data field. To specify two or more fields in the AddFields method, you wrap those fields in an array function.

Use this code to define the pivot table:

' Set up the row fields
PT.AddFields RowFields:="Customer", _
    ColumnFields:=Array("Date", "Data"), _
    PageFields:="Product"

This is the first time you have seen the PageFields parameter in this chapter. If you were creating a pivot table for someone to use, the fields in the PageField allow for easy ad hoc analysis. In this case, the value in the PageField is going to make it easy to replicate the report for every product-line manager.

Counting the Number of Records

So far, the .Function property of the data fields has always been xlSum. A total of 11 functions are available: xlSum, xlCount, xlAverage, xlStdDev, xlMin, xlMax, and so on.

Count is the only function that works for text fields. To count the number of records, and hence the number of orders, add a text field to the data area and choose xlCount as the function:

With PT.PivotFields("Region")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    .NumberFormat = "#,##0"
    .Name = "# of Orders "
End With

Grouping Daily Dates to Months, Quarters, or Years

Pivot tables have the amazing capability to group daily dates up to months, quarters, and/or years. In VBA, this feature is a bit annoying because you must select a date cell before issuing the command. As you saw in Figure 12.2, your pivot table usually stays as four blank cells until the end of the macro, so there really is not a date field to select.

However, if you need to group a date field, you have to let the pivot table redraw. To do this, use this code:

' Pause here to group daily dates up to years
' Need to draw the pivot table so you can select date heading
PT.ManualUpdate = False
PT.ManualUpdate = True

There are seven ways to group times or dates: Seconds, Minutes, Hours, Days, Months, Quarters, and Years. Note that you can group a field by multiple items. You specify a series of seven True/False values corresponding to Seconds, Minutes, and so on.

For example, to group by Months, Quarters, and Years, you would use the following:

PT.PivotFields("Date").LabelRange.Group , Periods:= _
    Array(False, False, False, False, True, True, True)

If you want to group by week, you group only by day and use 7 as the value for the By parameter:

PT.PivotFields("Date").LabelRange.Group _
    Start:=True, End:=True, By:=7, _
    Periods:=Array(False, False, False, True, False, False, False)

Specifying True for Start and End starts the first week at the earliest date in the data. If you want to show only the weeks from Monday December 30, 2013, to Sunday, January 3, 2016, use this code:

With PT.PivotFields("Date")
    .LabelRange.Group _
        Start:=DateSerial(2013, 12, 30), _
        End:=DateSerial(2016, 1, 3), _
        By:=7, _
        Periods:=Array(False, False, False, True, False, False, False)
    On Error Resume Next
    .PivotItems("<12/30/2013").Visible = False
    .PivotItems(">1/3/2016").Visible = False
    On Error Goto 0
End With

For this report, you need to group only by year, so the code is as follows:

' Group daily dates up to years
PT.PivotFields("Date").LabelRange.Group , Periods:= _
    Array(False, False, False, False, False, False, True)

After you group daily dates to years, the new Year field is still called Date. This might not always be true. If you roll daily dates up to months and to years, the Date field contains months, and a new Year field is added to the field list to hold years.

Changing the Calculation to Show Percentages

Excel 2013 offers 15 choices on the Show Values As tab of the Value Field Settings dialog.

These calculations enable you to change how a field is displayed in the report. Instead of showing sales, you could show the sales as a percentage of the total sales. You could show a running total. You could show each day’s sales as a percentage of the previous day’s sales.

All these settings are controlled through the .Calculation property of the pivot field. Each calculation has its own unique set of rules. Some, such as % of Column, work without any further settings. Others, such as Running Total In, require a base field. Others, such as Running Total, require a base field and a base item.

To get the percentage of the total, specify xlPercentOfTotal as the .Calculation property for the page field:

.Calculation = xlPercentOfTotal

To set up a running total, you have to specify a BaseField. Say that you need a running total along a date column:

' Set up Running Total
    .Calculation = xlRunningTotal
    .BaseField = "Date"

With ship months going down the columns, you might want to see the percentage of revenue growth from month to month. You can set up this arrangement with the xlPercentDifferenceFrom setting. In this case, you must specify that the BaseField is "Date" and that the BaseItem is something called (previous):

' Set up % change from prior month
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "%Change"
    .Calculation = xlPercentDifferenceFrom
    .BaseField = "Date"
    .BaseItem = "(previous)"
    .NumberFormat = "#0.0%"
End With

Note that with positional calculations, you cannot use the AutoShow or AutoSort method. This is too bad; it would be interesting to sort the customers high to low and to see their sizes in relation to each other.

You can use the xlPercentDifferenceFrom setting to express revenues as a percentage of the West region sales:

' Show revenue as a percentage of California
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "% of West"
    .Calculation = xlPercentDifferenceFrom
    .BaseField = "State"
    .BaseItem = "California"
    .Position = 3
    .NumberFormat = "#0.0%"
End With

Table 12.1 shows the complete list of .Calculation options. The second column indicates whether the calculation is compatible with earlier versions of Excel. The third column indicates whether you need a base field and base item.

Table 12.1. Complete List of .Calculation Options

After that long explanation of the .Calculation property, you can build the other two pivot table fields for the product line report.

Add Revenue to the report twice. The first time, there is no calculation. The second time, calculate the percentage of total:

' Set up the data fields - Revenue
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 2
    .NumberFormat = "#,##0"
    .Name = "Revenue "
End With

' Set up the data fields - % of total Revenue
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 3
    .NumberFormat = "0.0%"
    .Name = "% of Total "
    .Calculation = xlPercentOfColumn
End With

Eliminating Blank Cells in the Values Area

If you have some customers who were new in year 2, their sales will appear blank in year 1. Anyone using Excel 97 or later can replace blank cells with zeros. In the Excel interface, you can find the setting on the Layout & Format tab of the PivotTable Options dialog box. Select the For Empty Cells, Show option and type 0 in the box.

The equivalent operation in VBA is to set the NullString property for the pivot table to "0":

PT.NullString = "0"

Controlling the Sort Order with AutoSort

The Excel interface offers an AutoSort option that enables you to show customers in descending order based on revenue. The equivalent code in VBA to sort the product field by descending revenue uses the AutoSort method:

PT.PivotFields("Customer").AutoSort Order:=xlDescending, _
    Field:="Revenue "

After applying some formatting in the macro, you now have one report with totals for all products, as shown in Figure 12.7.

Figure 12.7. The Product drop-down in column K enables you to filter the report to certain products.

Replicating the Report for Every Product

As long as your pivot table was not built on an OLAP data source, you now have access to one of the most powerful, but least well-known, features in pivot tables. The command is called Show Report Filter Pages, and it takes your pivot table and replicates it for every item in one of the fields in the Filters area.

Because you built the report with Product as a filter field, it takes only one line of code to replicate the pivot table for every product:

' Replicate the pivot table for each product
PT.ShowPages PageField:="Product"

After running this line of code, you have a new worksheet for every product in the dataset. Starting in Excel 2013, the new product pivot tables inherit the .ManualUpdate setting from the original pivot table. Be sure to set the original pivot table to .ManualUpdate = False before using the command; otherwise, the new pivot tables do not draw correctly.

From there, you have some simple formatting and calculations. Check the end of the macro for these techniques, which should be second nature by this point in the book.

Listing 12.3 shows the complete macro.

Listing 12.3. Produce Report per Product

---

Sub CustomerByProductReport()
    ' Use a Pivot Table to create a report for each product
    ' with customers in rows and years in columns
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PT2 As PivotTable
    Dim WS As Worksheet
    Dim WSF As Worksheet
    Dim PRange As Range
    Dim FinalRow As Long
    Set WSD = Worksheets("PivotTable")

    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    WSD.Range("J1:Z1").EntireColumn.Clear

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row fields
    PT.AddFields RowFields:="Customer", _
        ColumnFields:=Array("Date", "Data"), _
        PageFields:="Product"

    ' Set up the data fields - count of orders
    With PT.PivotFields("Region")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
        .NumberFormat = "#,##0"
        .Name = "# of Orders "
    End With

    ' Pause here to group daily dates up to years
    ' Need to draw the pivot table so you can select date heading
    PT.ManualUpdate = False
    PT.ManualUpdate = True

    ' Group daily dates up to years
    PT.PivotFields("Date").LabelRange.Group , Periods:= _
        Array(False, False, False, False, False, False, True)

    ' Set up the data fields - Revenue
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2
        .NumberFormat = "#,##0"
        .Name = "Revenue "
    End With

    ' Set up the data fields - % of total Revenue
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 3
        .NumberFormat = "0.0%"
        .Name = "% of Total "
        .Calculation = xlPercentOfColumn
    End With

    ' Sort the customers so the largest is at the top
    PT.PivotFields("Customer").AutoSort Order:=xlDescending, _
        Field:="Revenue "

    With PT
        .ShowTableStyleColumnStripes = True
        .ShowTableStyleRowStripes = True
        .TableStyle2 = "PivotStyleMedium10"
        .NullString = "0"
    End With

    ' Calc the pivot table
    PT.ManualUpdate = False

    ' Replicate the pivot table for each product
    PT.ShowPages PageField:="Product"

    Ctr = 0
    For Each WS In ActiveWorkbook.Worksheets
        If WS.PivotTables.Count > 0 Then
            If WS.Cells(1, 1).Value = "Product" Then
                ' Save some info
                WS.Select
                ThisProduct = Cells(1, 2).Value
                Ctr = Ctr + 1
                If Ctr = 1 Then
                    Set WSF = ActiveSheet
                End If
                Set PT2 = WS.PivotTables(1)
                CalcRows = PT2.TableRange1.Rows.Count - 3

                PT2.TableRange2.Copy
                PT2.TableRange2.PasteSpecial xlPasteValues

                Range("A1:C3").ClearContents
                Range("A1:B2").Clear
                Range("A1").Value = "Product report for " & ThisProduct
                Range("A1").Style = "Title"

                ' Fix some headings
                Range("b5:d5").Copy Destination:=Range("H5:J5")
                Range("H4").Value = "Total"
                Range("I4:J4").Clear

                ' Copy the format
                Range("J1").Resize(CalcRows + 5, 1).Copy
                Range("K1").Resize(CalcRows + 5, 1). _
                    PasteSpecial xlPasteFormats
                Range("K5").Value = "% Rev Growth"
                Range("K6").Resize(CalcRows, 1).FormulaR1C1 = _
                   "=IFERROR(RC6/RC3-1,1)"

                Range("A2:K5").Style = "Heading 4"
                Range("A2").Resize(CalcRows + 10, 11).Columns.AutoFit

            End If
        End If
    Next WS

    WSD.Select
    PT.TableRange2.Clear
    Set PTCache = Nothing

    WSF.Select
    MsgBox Ctr & " product reports created."

End Sub

---

Filtering a Dataset

There are many ways to filter a pivot table, from the new slicers, to the conceptual filters, to simply selecting and clearing items from one of the many field drop-downs.

Manually Filtering Two or More Items in a Pivot Field

When you open a field heading drop-down and select or clear items from the list, you are applying a manual filter. (See Figure 12.8.)

Figure 12.8. This filter drop-down offers manual filters, a search box, and conceptual filters.

For example, you have one client who sells shoes. In the report showing sales of sandals, he wants to see just the stores that are in warm-weather states. The code to hide a particular store is as follows:

PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False

This process is easy in VBA. After building the table with Product in the page field, loop through to change the Visible property to show only the total of certain products:

' Make sure all PivotItems along line are visible
For Each PivItem In _
    PT.PivotFields("Product").PivotItems
    PivItem.Visible = True
Next PivItem

' Now - loop through and keep only certain items visible
For Each PivItem In _
    PT.PivotFields("Product").PivotItems
    Select Case PivItem.Name
        Case "Landscaping/Grounds Care", _
            "Green Plants and Foliage Care"
            PivItem.Visible = True
        Case Else
            PivItem.Visible = False
    End Select
Next PivItem

Using the Conceptual Filters

Excel 2007 introduced new conceptual filters for date fields, numeric fields, and text fields. Open the drop-down for any field label in the pivot table. In the drop-down that appears, you can choose Label Filters, Date Filters, or Value Filters. The date filters offer the capability to filter to a conceptual period such as last month or next year (see Figure 12.9).

Figure 12.9. These date filters were introduced in Excel 2007.

To apply a label filter in VBA, use the PivotFilters.Add method. The following code filters to the customers that start with the letter E:

PT.PivotFields("Customer").PivotFilters.Add _
    Type:=xlCaptionBeginsWith, Value1:="E"

To clear the filter from the Customer field, use the ClearAllFilters method:

PT.PivotFields("Customer").ClearAllFilters

To apply a date filter to the date field to find records from this week, use this code:

PT.PivotFields("Date").PivotFilters.Add Type:=xlThisWeek

The value filters enable you to filter one field based on the value of another field. For example, to find all the markets where the total revenue is more than $100,000, use this code:

PT.PivotFields("Market").PivotFilters.Add _
    Type:=xlValueIsGreaterThan, _
    DataField:=PT.PivotFields("Sum of Revenue"), _
    Value1:=100000

Other value filters might enable you to specify that you want branches where the revenue is between $50,000 and $100,000. In this case, you specify one limit as Value1 and the second limit as Value2:

PT.PivotFields("Market").PivotFilters.Add _
    Type:=xlValueIsBetween, _
    DataField:=PT.PivotFields("Sum of Revenue"), _
    Value1:=50000, Value2:=100000

Table 12.2 lists all the possible filter types.

Table 12.2. Filter Types

Using the Search Filter

Excel 2010 added a Search box to the filter drop-down. Although this is a slick feature in the Excel interface, there is no equivalent magic in VBA. Whereas the drop-down offers the Select All Search Results check box, the equivalent VBA just lists all the items that match the selection.

There is nothing new in Excel 2013 VBA to emulate the search box. To achieve the same results in VBA, use the xlCaptionContains filter described in the code that precedes Table 12.2.

Setting Up Slicers to Filter a Pivot Table

Excel 2010 introduced the concept of slicers to filter a pivot table. A slicer is a visual filter. You can resize and reposition slicers. You can control the color of the slicer and control the number of columns in a slicer. You can also select or unselect items from a slicer using VBA.

Figure 12.11 shows a pivot table with five slicers. Three of the slicers are modified to show multiple columns.

Figure 12.11. Slicers provide a visual filter of several fields.

Slicers were new in Excel 2010 and work only with pivot tables designed to be used by Excel 2010 or newer. That introduces some changes to the previous code in this chapter.

All the previous examples created the PivotCache using the PivotCaches.Add method. This method works from Excel 2000 through the current version. The method is not documented, but it works. Starting in Excel 2007, Microsoft replaced the PivotCaches.Add method with PivotCaches.Create. When you switch from using .Add to using .Create, your code causes an error in Excel 2003. Because Excel 2003 is still a supported product, it seems dangerous to switch over to PivotCaches.Create.

However, if you want to use a slicer or a timeline, you are forced to switch over to PivotCaches.Create and then specify that you explicitly understand that the workbook works only in Excel 2010 or newer using the xlPivotTableVersion14 argument.

Here is the code for creating the PivotCache and the pivot table when you plan on using a slicer:

Set PTCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=PRange.Address, _
    Version:=xlPivotTableVersion14)
Set PT = PTCache.CreatePivotTable( _
    TableDestination:=Cells(6, FinalCol + 2), _
    TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14)

A slicer consists of a slicer cache and a slicer. To define a slicer cache, you need to specify a pivot table as the source and a field name as the SourceField. The slicer cache is defined at the workbook level. This would enable you to have the slicer on a different worksheet than the actual pivot table:

Dim SCP as SlicerCache
Dim SCR as SlicerCache
Set SCP = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="Product")
Set SCR = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="Region")

After you have defined the slicer cache, you can add the slicer. The slicer is defined as an object of the slicer cache. Specify a worksheet as the destination. The name argument controls the internal name for the slicer. The Caption argument is the heading that is visible in the slicer. This might be useful if you would like to show the name Region, but the IT department defined the field as IDKRegn. Specify the size of the slicer using height and width in points. Specify the location using top and left in points.

In the following code, the values for top, left, height, and width are assigned to be equal to the location or size of certain cell ranges:

Dim SLP as Slicer
Set SLP = SCP.Slicers.Add(SlicerDestination:=WSD, Name:="Product", _
    Caption:="Product", _
    Top:=WSD.Range("A12").Top, _
    Left:=WSD.Range("A12").Left + 10, _
    Width:=WSR.Range("A12:C12").Width, _
    Height:=WSD.Range("A12:A16").Height)

All slicers start out as one column. You can change the style and number of columns with this code:

' Format the color and number of columns
With SLP
    .Style = "SlicerStyleLight6"
    .NumberOfColumns = 5
End With

After the slicer is defined, you can actually use VBA to choose which items are activated in the slicer. It seems counterintuitive, but to choose items in the slicer, you have to change the SlicerItem, which is a member of the SlicerCache, not a member of the Slicer:

With SCP
    .SlicerItems("A292").Selected = True
    .SlicerItems("B722").Selected = True
    .SlicerItems("C409").Selected = False
    .SlicerItems("D625").Selected = False
    .SlicerItems("E438").Selected = False
End With

You might need to deal with slicers that already exist. If a slicer is created for the product field, the name of the SlicerCache is "Slicer_Product". The following code formats existing slicers:

Sub MoveAndFormatSlicer()
    Dim SCP As SlicerCache
    Dim SLP as Slicer
    Dim WSD As Worksheet
    Set WSD = ActiveSheet
    Set SCP = ActiveWorkbook.SlicerCaches("Slicer_Product")
    Set SLP = SCS.Slicers("Product")
    With SLP
        .Style = "SlicerStyleLight6"
        .NumberOfColumns = 5
        .Top = WSD.Range("A1").Top + 5
        .Left = WSD.Range("A1").Left + 5
        .Width = WSD.Range("A1:B14").Width - 60
        .Height = WSD.Range("A1:B14").Height
    End With
End Sub

Setting Up a Timeline to Filter an Excel 2013 Pivot Table

Microsoft introduced the timeline slicer in Excel 2013. This is a special type of slicer. It is not compatible with Excel 2010 or earlier. In the language of VBA, the marketing name of Excel 2013 is really called Version 15.

Whereas the other examples in this chapter use the .Add method for adding a pivot cache, this example must use the .Create method for defining the pivot table cache. The difference? The .Create method enables you to specify DefaultVersion:=xlPivotTable Version15, whereas the .Add method does not. If you do not define the pivot cache and pivot table as destined for version 15, the Insert Timeline icon is grayed out.

Here is the code to set up a pivot cache and a pivot table that allows a timeline control:

' Define the pivot table cache
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=PRange.Address, _
    Version:=xlPivotTableVersion15)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable( _
    TableDestination:=Cells(10, FinalCol + 2), _
    TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion15)

Later, after adding fields to your pivot table, you define a slicer cache and specify the type as xlTimeLine:

' Define the Slicer Cache
' First two arguments are Source and SourceField
' Third argument, Name should be skipped
Set SC = WBD.SlicerCaches.Add(PT, "ShipDate", , _
    SlicerCacheType:=xlTimeline)

The slicer is then added to the Slicer Cache:

' Define the timeline as a slicer
Set SL = SC.Slicers.Add(WSD, , _
    Name:="ShipDate", _
    Caption:="Year", _
    Top:=WSD.Range("J1").Top, _
    Left:=WSD.Range("J1").Left, _
    Width:=262.5, Height:=108)

Timelines can exist at the day, month, quarter, or year level. To change the level of the timeline, use the TimelineViewState.Level property:

SL.TimelineViewState.Level = xlTimelineLevelYears

To actually filter the timeline to certain dates, you have to use the TimelineState.SetFilterDataRange property, which applies to the Slicer Cache:

SC.TimelineState.SetFilterDateRange "1/1/2014", "12/31/2015"

Listing 12.4 shows the complete macro to build a version 15 pivot table and add a timeline slicer.

Listing 12.4. Pivot with Timeline

---

    Sub PivotWithYearSlicer()
    Dim SC As SlicerCache
    Dim SL As Slicer
    Dim WSD As Worksheet
    Dim WSR As Worksheet
    Dim WBD As Workbook
    Dim PT As PivotTable
    Dim PTCache As PivotCache
    Dim PRange As Range
    Dim FinalRow As Long
    Set WBD = ActiveWorkbook
    Set WSD = Worksheets("Data")

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

    ' Delete any prior slicer cache
    For Each SC In ActiveWorkbook.SlicerCaches
        SC.Delete
    Next SC

    ' Define input area and set up a Pivot Cache
    WSD.Select
    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)

    ' Define the pivot table cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=PRange.Address, _
        Version:=xlPivotTableVersion15)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable( _
        TableDestination:=Cells(10, FinalCol + 2), _
        TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion15)

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row & column fields
    PT.AddFields RowFields:=Array("Customer")

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

    PT.ManualUpdate = False

    ' Define the Slicer Cache
    ' First two arguments are Source and SourceField
    ' Third argument, Name should be skipped
    Set SC = WBD.SlicerCaches.Add(PT, "ShipDate", , _
        SlicerCacheType:=xlTimeline)

    ' Define the timeline as a slicer
    Set SL = SC.Slicers.Add(WSD, , _
        Name:="ShipDate", _
        Caption:="Year", _
        Top:=WSD.Range("J1").Top, _
        Left:=WSD.Range("J1").Left, _
        Width:=262.5, Height:=108)

    ' Set the timeline to show years
    SL.TimelineViewState.Level = xlTimelineLevelYears

    ' Set the dates for the timeline
    SC.TimelineState.SetFilterDateRange "1/1/2014", "12/31/2014"
End Sub

---

Figure 12.12 shows the timeline slicer.

Figure 12.12. Timelines are new in Excel 2013 and require special pivot table code to exist.

Using the Data Model in Excel 2013

Excel 2013 incorporates parts of PowerPivot into the core Excel product. Items in the Excel ribbon are incorporated into the Data Model. Items in the PowerPivot ribbon are not. This means you can add two tables to the Data Model, create a relationship, and then build a pivot table from the Data Model.

To follow along with this example, open the 12-BeforeDataModel.xlsm file from the sample download files. This workbook has two tables: Sales and Sector. Sector is a lookup table that is related to the Sales table via a customer field. To build the pivot table, follow these general steps in the macro:

1. Add the main table to the model.

2. Add the lookup table to the model.

3. Link the two tables with a relationship.

4. Create a pivot cache from ThisWorkbookDataModel.

5. Create a pivot table from the cache.

6. Add row fields.

7. Define a measure. Add the measure to the pivot table.

Adding Both Tables to the Data Model

You should already have a dataset in the workbook that has been converted to a table using the Ctrl+T shortcut. On the Table Tools Design tab, change the table name to Sales. To link this table to the Data Model, use this code:

' Build Connection to the main Sales table
Set WBT = ActiveWorkbook
TableName = "Sales"
WBT.Connections.Add Name:="LinkedTable_" & TableName, _
    Description:="", _
    ConnectionString:="WORKSHEET;" & WBT.FullName, _
    CommandText:=WBT.Name & "!" & TableName, _
    lCmdType:=7, _
    CreateModelConnection:=True, _
    ImportRelationships:=False

There are several variables in that code that use the table name, the workbook path, and/or the workbook name. By storing the table name in a variable at the top of the code, you can build the connection name, connection string, and command text using the variables.

Adapting the preceding code to link to the lookup table then requires only changing the TableName variable:

TableName = "Sector"
WBT.Connections.Add Name:="LinkedTable_" & TableName, _
    Description:="", _
    ConnectionString:="WORKSHEET;" & WBT.FullName, _
    CommandText:=WBT.Name & "!" & TableName, _
    lCmdType:=7, _
    CreateModelConnection:=True, _
    ImportRelationships:=False

Creating a Relationship Between the Two Tables

When you create a relationship in the Excel interface, you specify four items in the Create Relationship dialog box. (See Figure 12.13.)

Figure 12.13. To create a relationship, specify a field in both tables.

The code to create the relationship is more streamlined. There can be only one Data Model per workbook. Set an object variable MO to refer to the model in this workbook. Use the ModelRelationships.Add method, specifying the two fields that are linked:

' Relate the two tables
Dim MO As Model
Set MO = ActiveWorkbook.Model
MO.ModelRelationships.Add _
    ForeignKeyColumn:=MO.ModelTables("Sales").ModelTableColumns("Customer"), _
    PrimaryKeyColumn:=MO.ModelTables("Sector").ModelTableColumns("Customer")

Defining the PivotCache and Building the Pivot Table

The code to define the pivot cache specifies that the data is external. Even though the linked tables are in your workbook, and even though the Data Model is stored as a binary large object within the workbook, this is still considered an external data connection. The connection is always called ThisWorkbookDataModel.

' Define the PivotCache
Set PTCache = WBT.PivotCaches.Create(SourceType:=xlExternal, _
    SourceData:=WBT.Connections("ThisWorkbookDataModel"), _
    Version:=xlPivotTableVersion15)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable( _
    TableDestination:=WSD.Cells(1, 1), TableName:="PivotTable1")

Adding Model Fields to the Pivot Table

There are really two types of fields you will add to the pivot table. Text fields such as Customer, Sector, or Product are simply fields that can be added to the row or column area of the pivot table. No calculation has to happen to these fields. The code for adding text fields is shown in this section. When you add a numeric field to the values area in the Excel interface, you are actually implicitly defining a new calculated field. To do this in VBA, you have to explicitly define the field and then add it.

First, the simpler example of adding a text field to the row area. The VBA code generically looks like this:

With PT.CubeFields("[TableName].[FieldName]")
    .Orientation = xlRowField
    .Position = 1
End With

In the current example, add the Sector field from the Sector table using this code:

With PT.CubeFields("[Sector].[Sector]")
    .Orientation = xlRowField
    .Position = 1
End With

Adding Numeric Fields to the Values Area

In Excel 2010, PowerPivot Calculated Fields were called Measures. In Excel 2013, the Excel interface calls them Calculations. However, the underlying VBA code still calls them Measures.

If you have a Data Model pivot table and you check the Revenue field, you see the Revenue Field move to the Values area. Behind the scenes, though, Excel is implicitly defining a new measure called Sum of Revenue. (You can see the implicit measures in the PowerPivot window if you have Excel 2013 Pro Plus.) In VBA, your first step is to define a new measure for Sum of Revenue. To make it easier to refer to this measure later, assign the new measure to an object variable:

' Before you can add Revenue to the pivot table,
' you have to define the measure.
' This happens using the GetMeasure method.
' Assign the cube field to CFRevenue object
Dim CFRevenue As CubeField
Set CFRevenue = PT.CubeFields.GetMeasure( _
    AttributeHierarchy:="[Sales].[Revenue]", _
    Function:=xlSum, _
    Caption:="Sum of Revenue")
' Add the newly created cube field to the pivot table
PT.AddDataField Field:=CFRevenue, _
    Caption:="Total Revenue"
PT.PivotFields("Total Revenue").NumberFormat = "$#,##0,K"

You can use the preceding sample to create a new measure. The following measure uses the new Distinct Count function to count the number of unique customers in each sector:

' Add Distinct Count of Customer as a Cube Field
Dim CFCustCount As CubeField
Set CFCustCount = PT.CubeFields.GetMeasure( _
    AttributeHierarchy:="[Sales].[Customer]", _
    Function:=xlDistinctCount, _
    Caption:="Customer Count")
' Add the newly created cube field to the pivot table
PT.AddDataField Field:=CFCustCount, _
    Caption:="Customer Count"

Caution: Before you get too excited, the Excel team drew an interesting line in the sand with regard to what parts of PowerPivot are available via VBA. Any functionality that is available in Office 2013 Standard is available in VBA. If you try to define a new calculated field that uses the PowerPivot DAX formula language, it will not work in VBA.

Putting It All Together

Figure 12.14 shows the Data Model pivot table created using the code in Listing 12.5.

Figure 12.14. Two tables linked with a pivot table and two measures via a macro.

Listing 12.5. Code to Create a Data Model Pivot Table

---

Sub BuildModelPivotTable()
    Dim WBT As Workbook
    Dim WC As WorkbookConnection
    Dim MO As Model
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim WSD As Worksheet
    Dim CFRevenue As CubeField
    Dim CFCustCount As CubeField

    Set WBT = ActiveWorkbook
    Set WSD = WBT.Worksheets("Report")

    ' Build Connection to the main Sales table
    TableName = "Sales"
    WBT.Connections.Add Name:="LinkedTable_" & TableName, _
        Description:="MainTable", _
        ConnectionString:="WORKSHEET;" & WBT.FullName, _
        CommandText:=WBT.Name & "!" & TableName, _
        lCmdType:=7, _
        CreateModelConnection:=True, _
        ImportRelationships:=False

    ' Build Connection to the Sector lookup table
    TableName = "Sector"
    WBT.Connections.Add Name:="LinkedTable_" & TableName, _
        Description:="LookupTable", _
        ConnectionString:="WORKSHEET;" & WBT.FullName, _
        CommandText:=WBT.Name & "!" & TableName, _
        lCmdType:=7, _
        CreateModelConnection:=True, _
        ImportRelationships:=False

    ' Relate the two tables
    Set MO = ActiveWorkbook.Model
    MO.ModelRelationships.Add _
        ForeignKeyColumn:=MO.ModelTables("Sales").ModelTableColumns("Customer"), _
        PrimaryKeyColumn:=MO.ModelTables("Sector").ModelTableColumns("Customer")

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

    ' Define the PivotCache
    Set PTCache = WBT.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=WBT.Connections("ThisWorkbookDataModel"), _
        Version:=xlPivotTableVersion15)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable( _
        TableDestination:=WSD.Cells(1, 1), TableName:="PivotTable1")

    ' Add the Sector field from the Sector table to the Row areas
    With PT.CubeFields("[Sector].[Sector]")
        .Orientation = xlRowField
        .Position = 1
    End With

    ' Before you can add Revenue to the pivot table,
    ' you have to define the measure.
    ' This happens using the GetMeasure method
    ' Assign the cube field to CFRevenue object
    Set CFRevenue = PT.CubeFields.GetMeasure( _
        AttributeHierarchy:="[Sales].[Revenue]", _
        Function:=xlSum, _
        Caption:="Sum of Revenue")
    ' Add the newly created cube field to the pivot table
    PT.AddDataField Field:=CFRevenue, _
        Caption:="Total Revenue"
    PT.PivotFields("Total Revenue").NumberFormat = "$#,##0,K"

    ' Add Distinct Count of Customer as a Cube Field
    Set CFCustCount = PT.CubeFields.GetMeasure( _
        AttributeHierarchy:="[Sales].[Customer]", _
        Function:=xlDistinctCount, _
        Caption:="Customer Count")
    ' Add the newly created cube field to the pivot table
    PT.AddDataField Field:=CFCustCount, _
        Caption:="Customer Count"

End Sub

---

Using Other Pivot Table Features

This section covers a few additional features in pivot tables that you might need to code with VBA.

Calculated Data Fields

Pivot tables offer two types of formulas. The most useful type defines a formula for a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level. If you define a calculated field for average price as revenue divided by units sold, Excel first adds the total revenue and total quantity, and then it does the division of these totals to get the result. In many cases, this is exactly what you need. If your calculation does not follow the associative law of mathematics, it might not work as you expect.

To set up a Calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula.

' Define Calculated Fields
    PT.CalculatedFields.Add Name:="ProfitPercent", Formula:="=Profit/Revenue"
    With PT.PivotFields("ProfitPercent")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 3
        .NumberFormat = "#0.0%"
        .Name = "GP Pct"
    End With

Calculated Items

Suppose you have a Measure field with two items: Budget and Actual. You would like to add a new position to calculate Variance as Actual-Budget. You can do this with a calculated item by using this code:

' Define calculated item along the product dimension
PT.PivotFields("Measure").CalculatedItems _
    .Add "Variance", "='Actual'-'Budget'"

Using ShowDetail to Filter a Recordset

When you take any pivot table in the Excel user interface and then double-click any number in the table, Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to perform a drill-down query into a dataset.

The equivalent VBA property is ShowDetail. By setting this property to True for any cell in the pivot table, you generate a new worksheet with all the records that make up that cell:

PT.TableRange2.Offset(2, 1).Resize(1, 1).ShowDetail = True

Changing the Layout from the Design Tab

The Layout group of the Design tab contains four drop-downs that control the following:

• Location of subtotals (top or bottom)

• Presence of grand totals

• Report layout including whether outer row labels are repeated

• Presence of blank rows

Subtotals can appear either at the top or at the bottom of a group of pivot items. The SubtotalLocation property applies to the entire pivot table; valid values are xlAtBottom or xlAtTop:

PT.SubtotalLocation:=xlAtTop

Grand totals can be turned on or off for rows or columns. Because these two settings can be confusing, remember that at the bottom of a report, there is a total line that most people would call the Grand Total Row. To turn off that row, you have to use the following:

PT.ColumnGrand = False

You need to turn off the ColumnGrand when you want to suppress the total row because Microsoft calls that row the “grand total for columns.” Get it? In other words, they are saying that the row at the bottom contains the total of the columns above it. I finally started doing better when I would decide which one to turn off, and then turn off the opposite one.

To suppress what you would call the Grand Total Column along the right side of the report, you have to suppress what Microsoft calls the Total for Rows with the following code:

PT.RowGrand = False

Settings for the Report Layout

There are three settings for the report layout:

• Tabular layout—Similar to the default layout in Excel 2003

• Outline layout—Optionally available in Excel 2003

• Compact layout—Introduced in Excel 2007

When you create a pivot table in the Excel interface, you get the compact layout. When you build a pivot table in VBA, you get the tabular layout. You can change to one of the other layouts with one of these lines:

PT.RowAxisLayout xlTabularRow
PT.RowAxisLayout xlOutlineRow
PT.RowAxisLayout xlCompactRow

Starting in Excel 2007, you can add a blank line to the layout after each group of pivot items. Although the Design tab offers a single setting to affect the entire pivot table, the setting is actually applied individually to each pivot field. The macro recorder responds by recording a dozen lines of code for a pivot table with 12 fields. You can intelligently add a single line of code for the outer Row fields:

PT.PivotFields("Region").LayoutBlankLine = True

Suppressing Subtotals for Multiple Row Fields

As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. That extra row field can get in the way if you plan to reuse the results of the pivot table as a new dataset for some other purpose. Although accomplishing this task manually can be relatively simple, the VBA code to suppress subtotals is surprisingly complex.

Most people do not realize that it is possible to show multiple types of subtotals. For example, you can choose to show Total, Average, Min, and Max in the same pivot table.

To suppress subtotals for a field, you must set the Subtotals property equal to an array of 12 False values. The first False turns off automatic subtotals, the second False turns off the Sum subtotal, the third False turns off the Count subtotal, and so on. This line of code suppresses the Region subtotal:

PT.PivotFields("Region").Subtotals = Array(False, False, False, False, _
    False, False, False, False, False, False, False, False)

A different technique is to turn on the first subtotal. This method automatically turns off the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:

PT.PivotFields("Region").Subtotals(1) = True
PT.PivotFields("Region").Subtotals(1) = False

Next Steps

If you cannot already tell, pivot tables are my favorite feature in Excel. They are incredibly powerful and flexible. Combined with VBA, they provide an excellent calculation engine and power many of the reports I build for clients. In Chapter 13, “Excel Power,” you’ll learn multiple techniques for handling various tasks in VBA.