Chapter 15: Working with Pivot Tables

In This Chapter

• Creating pivot tables with VBA

• Looking at examples of VBA procedures that create pivot tables

• Using VBA to create a worksheet table from a summary table

An Introductory Pivot Table Example

Excel's pivot table feature is, arguably, the most innovative and powerful feature in Excel. Pivot tables first appeared in Excel 5, and the feature has been improved in every subsequent version. This chapter is not an introduction to pivot tables. I assume that you're familiar with this feature and its terminology and that you know how to create and modify pivot tables manually.

As you probably know, creating a pivot table from a database or list enables you to summarize data in ways that otherwise would not be possible — and is amazingly fast and requires no formulas. You also can write VBA code to generate and modify pivot tables.

This section gets the ball rolling with a simple example of using VBA to create a pivot table.

Figure 15-1 shows a simple worksheet range that contains four fields: SalesRep, Region, Month, and Sales. Each record describes the sales for a particular sales representative in a particular month.

This workbook, named simple pivot table.xlsm, is available on the book's website.

Figure 15-1: This table is a good candidate for a pivot table.

Creating a pivot table

Figure 15-2 shows a pivot table created from the data, along with the PivotTable Field List task pane. This pivot table summarizes the sales performance by sales representative and month. This pivot table is set up with the following fields:

• Region: A report filter field in the pivot table

• SalesRep: A row field in the pivot table

Figure 15-2: A pivot table created from the data in Figure 15-1.

• Month: A column field in the pivot table

• Sales: A values field in the pivot table that uses the Sum function

I turned on the macro recorder before I created this pivot table and specified a new worksheet for the pivot table location. The code that was generated follows:

Sub CreatePivotTable()

    Sheets.Add

    ActiveWorkbook.PivotCaches.Create _

        (SourceType:=xlDatabase, _

        SourceData:=”Sheet1!R1C1:R13C4”, _

        Version:=xlPivotTableVersion15).CreatePivotTable _

        TableDestination:=”Sheet2!R3C1”, _

        TableName:=”PivotTable1”, _

        DefaultVersion:=xlPivotTableVersion15

    Sheets(“Sheet2”).Select

    Cells(3, 1).Select

    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Region”)

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“SalesRep”)

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Month”)

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables(“PivotTable1”).AddDataField _

        ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Sales”), _

        “Sum of Sales”, xlSum

End Sub

If you execute this macro, it will almost certainly end with an error. Examine the code, and you'll see that the macro recorder hard-coded the worksheet name (Sheet2) for the pivot table. If that sheet already exists (or if the new sheet that's added has a different name), the macro ends with an error. But a more serious problem is that the macro recorder also hard-coded the pivot table name. The new pivot table's name won't be PivotTable1 if the workbook has other pivot tables.

But even though the recorded macro doesn't work, it's not completely useless. The code provides lots of insight for writing code to generate pivot tables.

Examining the recorded code for the pivot table

VBA code that works with pivot tables can be confusing. to make any sense of the recorded macro, you need to know about a few relevant objects, all of which are explained in the Help system.

• PivotCaches: A collection of PivotCache objects in a Workbook object (the data used by a pivot table is stored in a pivot cache).

• PivotTables: A collection of PivotTable objects in a Worksheet object.

• PivotFields: A collection of fields in a PivotTable object.

• PivotItems: A collection of individual data items within a field category.

• CreatePivotTable: A method that creates a pivot table by using the data in a pivot cache.

Cleaning up the recorded pivot table code

As with most recorded macros, the preceding example isn't as efficient as it could be. And, as I noted, it's likely to generate an error. You can simplify the code to make it more understandable and also to prevent the error. The hand-crafted code that follows generates the same pivot table as the procedure previously listed:

Sub CreatePivotTable()

    Dim PTCache As PivotCache

    Dim PT As PivotTable

    

‘   Create the cache

    Set PTCache = ActiveWorkbook.PivotCaches.Create( _

        SourceType:=xlDatabase, _

        SourceData:=Range(“A1”).CurrentRegion)

    

‘   Add a new sheet for the pivot table

    Worksheets.Add

    

‘   Create the pivot table

    Set PT = ActiveSheet.PivotTables.Add( _

        PivotCache:=PTCache, _

        TableDestination:=Range(“A3”))

    

‘   Specify the fields

    With PT

        .PivotFields(“Region”).Orientation = xlPageField

        .PivotFields(“Month”).Orientation = xlColumnField

        .PivotFields(“SalesRep”).Orientation = xlRowField

        .PivotFields(“Sales”).Orientation = xlDataField

    

        ‘no field captions

        .DisplayFieldCaptions = False

    End With

End Sub

The CreatePivotTable procedure is simplified (and might be easier to understand) because it declares two object variables: PTCache and PT. A new PivotCache object is created by using the Create method. A worksheet is added, and it becomes the active sheet (the destination for the pivot table). Then a new PivotTable object is created by using the Add method of the PivotTables collection. The last section of the code adds the four fields to the pivot table and specifies their location within it by assigning a value to the Orientation property.

The original macro hard-coded both the data range used to create the PivotCache object (‘Sheet1!R1C1:R13C4') and the pivot table location (Sheet2). In the CreatePivotTable procedure, the pivot table is based on the current region surrounding cell A1. This ensures that the macro will continue to work properly if more data is added.

Adding the worksheet before the pivot table is created eliminates the need to hard-code the sheet reference. Yet another difference is that the hand-written macro doesn't specify a pivot table name. Because the PT object variable is created, your code doesn't ever have to refer to the pivot table by name.

The code could be more general through the use of indices rather than literal strings for the PivotFields collections. This way, if the user changes the column headings, the code will still work. For example, more general code would use PivotFields(1) rather than PivotFields(‘Region').

As always, the best way to master this topic is to record your actions in a macro to find out its relevant objects, methods, and properties. Then study the Help topics to understand how everything fits together. In almost every case, you'll need to modify the recorded macros. Or, after you understand how to work with pivot tables, you can write code from scratch and avoid the macro recorder.

Creating a More Complex Pivot Table

In this section, I present VBA code to create a relatively complex pivot table.

Figure 15-3 shows part of a large worksheet table. This table has 15,840 rows and consists of hierarchical budget data for a corporation. The corporation has 5 divisions, and each division contains 11 departments. Each department has 4 budget categories, and each budget category contains several budget items. Budgeted and actual amounts are included for each of the 12 months. The goal is to summarize this information with a pivot table.

This workbook is available on the book's website in a file named budget pivot table.xlsm.

Figure 15-4 shows a pivot table created from the data. Note that the pivot table contains a calculated field named Variance. This field is the difference between the Budget amount and the Actual amount.

Figure 15-3: The data in this workbook will be summarized in a pivot table.

Figure 15-4: A pivot table created from the budget data.

Another option is to insert a new column in the table and create a formula to calculate the difference between the budget and actual amounts. If the data is from an external source (rather than in a worksheet), that option may not be possible.

The code that created the pivot table

Here's the VBA code that created the pivot table:

Sub CreatePivotTable()

    Dim PTcache As PivotCache

    Dim PT As PivotTable

    

    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.Create( _

      SourceType:=xlDatabase, _

      SourceData:=Range(“A1”).CurrentRegion.Address)

    

‘   Add new worksheet

    Worksheets.Add

    ActiveSheet.Name = “PivotSheet”

    ActiveWindow.DisplayGridlines = False

    

‘   Create the Pivot Table from the Cache

    Set PT = ActiveSheet.PivotTables.Add( _

      PivotCache:=PTcache, _

      TableDestination:=Range(“A1”), _

      TableName:=”BudgetPivot”)

    

    With PT

‘       Add fields

        .PivotFields(“Category”).Orientation = xlPageField

        .PivotFields(“Division”).Orientation = xlPageField

        .PivotFields(“Department”).Orientation = xlRowField

        .PivotFields(“Month”).Orientation = xlColumnField

        .PivotFields(“Budget”).Orientation = xlDataField

        .PivotFields(“Actual”).Orientation = xlDataField

        .DataPivotField.Orientation = xlRowField

    

‘       Add a calculated field to compute variance

        .CalculatedFields.Add “Variance”, “=Budget-Actual”

        .PivotFields(“Variance”).Orientation = xlDataField

        

‘       Specify a number format

        .DataBodyRange.NumberFormat = “0,000”

        

‘       Apply a style

        .TableStyle2 = “PivotStyleMedium2”

        

‘       Hide Field Headers

        .DisplayFieldCaptions = False

        

‘       Change the captions

        .PivotFields(“Sum of Budget”).Caption = “ Budget”

        .PivotFields(“Sum of Actual”).Caption = “ Actual”

        .PivotFields(“Sum of Variance”).Caption = “ Variance”

    End With

End Sub

How the more complex pivot table works

The CreatePivotTable procedure starts by deleting the PivotSheet worksheet if it already exists. It then creates a PivotCache object, inserts a new worksheet named PivotSheet, and creates the pivot table from the PivotCache. The code then adds the following fields to the pivot table:

• Category: A report filter (page) field

• Division: A report filter (page) field

• Department: A row field

• Month: A column field

• Budget: A data field

• Actual: A data field

Note that the Orientation property of the DataPivotField is set to xlRowField in the following statement:

.DataPivotField.Orientation = xlRowField

This statement determines the overall orientation of the pivot table, and it represents the Sum Values field in the Pivot Table Fields task pane (see Figure 15-5). Try moving that field to the Columns section to see how it affects the pivot table layout.

Figure 15-5: The Pivot Table Fields task pane.

Next, the procedure uses the Add method of the CalculatedFields collection to create the calculated field Variance, which subtracts the Actual amount from the Budget amount. This calculated field is assigned as a data field.

To add a calculated field to a pivot table manually, use the PivotTable⇒Options⇒Calculations⇒Fields, Items, & Sets⇒Calculated Field command, which displays the Insert Calculated Field dialog box.

Finally, the code makes a few cosmetic adjustments:

• Applies a number format to the DataBodyRange (which represents the entire pivot table data).

• Applies a style.

• Hides the captions (equivalent to the PivotTable Tools⇒Options⇒Show ⇒Field Headers command).

• Changes the captions displayed in the pivot table. For example, Sum of Budget is replaced by Budget. Note that the Budget string is preceded by a space. Excel doesn't allow you to change a caption that corresponds to a field name, so adding a space gets around this restriction.

While creating this procedure, I used the macro recorder extensively to learn about the various properties. That technique, combined with the information in the Help system (and a fair amount of trial and error), provided all the information I needed.

Creating Multiple Pivot Tables

The final example creates a series of pivot tables that summarize data collected in a customer survey. That data is stored in a worksheet (see Figure 15-6) and consists of 150 rows. Each row contains the respondent's sex plus a numerical rating using a 1–5 scale for each of the 14 survey items.

Figure 15-6: Creating a series of pivot tables will summarize this survey data.

This workbook, named survey data pivot tables.xlsm, is available on the book's website.

Figure 15-7 shows a few of the 28 pivot tables produced by the macro. Each survey item is summarized in 2 pivot tables (one showing percentages, and one showing the actual frequencies).

Figure 15-7: Several pivot tables created by a VBA procedure.

The VBA code that created the pivot tables follows:

Sub MakePivotTables()

‘   This procedure creates 28 pivot tables

    Dim PTCache As PivotCache

    Dim PT As PivotTable

    Dim SummarySheet As Worksheet

    Dim ItemName As String

    Dim Row As Long, Col As Long, i As Long

    

    Application.ScreenUpdating = False

    

‘   Delete Summary sheet if it exists

    On Error Resume Next

    Application.DisplayAlerts = False

    Sheets(“Summary”).Delete

    On Error GoTo 0

    

‘   Add Summary sheet

    Set SummarySheet = Worksheets.Add

    ActiveSheet.Name = “Summary”

    

‘   Create Pivot Cache

    Set PTCache = ActiveWorkbook.PivotCaches.Create( _

      SourceType:=xlDatabase, _

      SourceData:=Sheets(“SurveyData”).Range(“A1”). _

        CurrentRegion)

    

    Row = 1

    For i = 1 To 14

      For Col = 1 To 6 Step 5 ‘2 columns

        ItemName = Sheets(“SurveyData”).Cells(1, i + 2)

        With Cells(Row, Col)

            .Value = ItemName

            .Font.Size = 16

        End With

    

‘       Create pivot table

        Set PT = ActiveSheet.PivotTables.Add( _

          PivotCache:=PTCache, _

          TableDestination:=SummarySheet.Cells(Row + 1, Col))

        

‘       Add the fields

        If Col = 1 Then ‘Frequency tables

            With PT.PivotFields(ItemName)

              .Orientation = xlDataField

              .Name = “Frequency”

              .Function = xlCount

            End With

        Else ‘ Percent tables

        With PT.PivotFields(ItemName)

            .Orientation = xlDataField

            .Name = “Percent”

            .Function = xlCount

            .Calculation = xlPercentOfColumn

            .NumberFormat = “0.0%”

        End With

        End If

   

        PT.PivotFields(ItemName).Orientation = xlRowField

        PT.PivotFields(“Sex”).Orientation = xlColumnField

        PT.TableStyle2 = “PivotStyleMedium2”

        PT.DisplayFieldCaptions = False

        If Col = 6 Then

‘           add data bars to the last column

            PT.ColumnGrand = False

            PT.DataBodyRange.Columns(3).FormatConditions. _

               AddDatabar

        With pt.DataBodyRange.Columns(3).FormatConditions(1)

          .BarFillType = xlDataBarFillSolid

          .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0

          .MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1

        End With

        End If

     Next Col

        Row = Row + 10

   Next i

   

‘   Replace numbers with descriptive text

    With Range(“A:A,F:F”)

        .Replace “1”, “Strongly Disagree”

        .Replace “2”, “Disagree”

        .Replace “3”, “Undecided”

        .Replace “4”, “Agree”

        .Replace “5”, “Strongly Agree”

    End With

End Sub

Note that all these pivot tables were created from a single PivotCache object.

The pivot tables are created in a nested loop. The Col loop counter goes from 1 to 6 by using the Step parameter. The instructions vary a bit for the second column of pivot tables. Specifically, the pivot tables in the second column do the following:

• Display the count as a percent of the column

• Do not show grand totals for the rows

• Are assigned a number format

• Display conditional formatting data bars

The Row variable keeps track of the starting row of each pivot table. The final step is to replace the numeric categories in columns A and F with text. For example, 1 is replaced with Strongly Agree.

Creating a Reverse Pivot Table

A pivot table is a summary of data in a table. But what if you have a summary table, and you'd like to create a normalized table from the summary? Figure 15-8 shows an example. Range B2:F14 contains a summary table — similar to a very simple pivot table. Columns I:K contain a 48-row table created from the summary table. In the table, each row contains one data point, and the first two columns describe that data point. In other words, the transformed data is normalized. (See the sidebar, “Data appropriate for a pivot table,” earlier in this chapter.)

Figure 15-8: The summary table on the left will be converted to the table on the right.

Excel doesn't provide a way to transform a summary table into a normalized table, so it's a good job for a VBA macro. After I created this macro, I spent a bit more time and added a UserForm, shown in Figure 15-9. The UserForm gets the input and output ranges and also has an option to convert the output range to a table.

Figure 15-9: This dialog box asks the user for the ranges.

This workbook, named reverse pivot table.xlsm, is available on the book's website.

When the user clicks the OK button in the UserForm, VBA code validates the ranges and then calls the ReversePivot procedure with this statement:

Call ReversePivot(SummaryTable, OutputRange, cbCreateTable)

It passes three arguments:

• SummaryTable: A Range object that represents the summary table

• OutputRange: A Range object that represents the upper-left cell of the output range

• cbCreateTable: The Checkbox object on the UserForm

This procedure will work for any size summary table. The number of data rows in the output table will be equal to (r-1) * (c-1), where r and c represent the number of rows and columns in SummaryTable.

The code for the ReversePivot procedure follows:

Sub ReversePivot(SummaryTable As Range, _

  OutputRange As Range, CreateTable As Boolean)

    Dim r As Long, c As Long

    Dim OutRow As Long, OutCol As Long

    

‘   Convert the range

    OutRow = 2

    Application.ScreenUpdating = False

    OutputRange.Range(“A1:C3”) = Array(“Column1”, “Column2”, “Column3”)

    For r = 2 To SummaryTable.Rows.Count

        For c = 2 To SummaryTable.Columns.Count

            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)

            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)

            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)

            OutRow = OutRow + 1

        Next c

    Next r

    

‘   Make it a table?

    If CreateTable Then _

      ActiveSheet.ListObjects.Add xlSrcRange, _

         OutputRange.CurrentRegion, , xlYes

End Sub

The procedure is fairly simple. The code loops through the rows and columns in the input range and then writes the data to the output range. The output range will always have three columns. The OutRow variable keeps track of the current row in the output range. Finally, if the user checked the check box, the output range is converted to a table by using the Add method of the ListObjects collection.