8
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. We 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 8.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.
Figure 8.1 This table is a good candidate for a pivot table.
Creating a pivot table
Figure 8.2 shows a pivot table created from the data, along with the PivotTable Fields 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
- Month: A column field in the pivot table
- Sales: A values field in the pivot table that uses the SUM function
Figure 8.2 A pivot table created from the data in Figure 8.1.
If you were to record a macro in Excel 2016 while building the pivot table in Figure 8.2, the macro recorder would generate the following code:
Sub CreatePivotTable()
Sheets.Add
ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R13C4", _
Version:=6).CreatePivotTable _
TableDestination:="Sheet2!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=6
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 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.
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, we present VBA code to create a relatively complex pivot table.
Figure 8.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.
Figure 8.3 The data in this workbook will be summarized in a pivot table.
Figure 8.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 8.4 A pivot table created from the budget data.
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 8.5). Try moving that field to the Columns section to see how it affects the pivot table layout.
Figure 8.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.
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.
Creating Multiple Pivot Tables
The final example creates a series of pivot tables that summarize data collected in a customer survey. That survey data 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 8.6 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 8.6 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 8.7 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 8.7 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. For example, the UserForm, shown in Figure 8.8, gets the input and output ranges and also has an option to convert the output range to a table.
Figure 8.8 This dialog box asks the user for the ranges.
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.