Chapter 12
Using VBA to create pivot tables
In this chapter, you will:
Find out how pivot tables evolved
Build a pivot table
Use advanced pivot table features
Filter a data set
Use the Data Model in Excel
Use other pivot table features
Compare VBA to TypeScript
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 help you very quickly summarize massive amounts of data. The name pivot table comes from the ability you have to drag fields in the PivotTable Fields list and have them recalculate. You can use a basic pivot table to produce a concise summary in seconds. However, pivot tables come in so many varieties 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 don’t suggest that you use VBA to build pivot tables for a user; rather, I suggest that you use pivot tables as a means to an end—to extract a summary of data that you can then take on to better uses.
Understanding how pivot tables evolved over various Excel 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. These are some of the changes Microsoft has made in the most recent five versions:
Excel 2010 introduced slicers, Repeat All Item Labels, Named Sets, and several new calculation options:
xlPercentOfParentColumn,xlPercentOfParentRow,xlPercentRunningTotal,xlRankAscending, andxlRankDescending. These do not work in Excel 2007.Excel 2013 introduced timelines, the
xlDistinctCountfunction, and the Power Pivot Data Model. You can add tables to the Data Model, create a relationship, and produce a pivot table, but this code does not run in Excel 2010 or earlier.Excel 2016 introduced AutoGrouping for dates. Although this is automatic, it does not affect pivot tables built with VBA.
Excel 2019 introduced PivotTable defaults. This does not affect pivot tables built with VBA.
Recent changes in Microsoft 365 include the ability to format an intersection of labels in a certain color (for example, West region sales of Bananas).
Because of all the changes from version to version, you need to be extremely careful when writing code in Excel that might be run in other versions.
Note
Much of the code in this chapter works with Excel 2010 and newer. Although this book does not include code for Excel 2007, one Excel 2007 example has been included in the sample file for this chapter. The code listings from this chapter are available for download at MicrosoftPressStore.com/ExcelVBAMacros365/downloads.
Building a pivot table in Excel VBA
As I mentioned earlier, 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.
Note
Although the Excel user interface has names for the various sections of a pivot table, VBA code continues to refer to the old names. Microsoft made this choice because, otherwise, millions of lines of code would stop working in Excel 2007 because they would refer to, say, a page field rather than a filter field. Today, the four sections of a pivot table in the Excel user interface are Filter, Columns, Rows, and Values, but VBA continues to use the old terms: Page fields, Column fields, Row fields, and Data fields.
Defining the pivot cache
In the first part of this chapter, the data set is an eight-column by 5,000-row data set, 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 Power Pivot.
FIGURE 12-1 You can create summary reports from this data set.
In Excel 2010 and later, you first create 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.Create( _
SourceType:=xlDatabase, _
SourceData:=Prange, _
Version:=xlPivotTableVersion14)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", Version:=xlPivotTableVersion14)
In the CreatePivotTable method, you specify the output location and optionally give the table a name. After running the preceding code, you have a strange-looking blank pivot table like the one shown in Figure 12-2. You need 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.
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 area of the PivotTable Fields list. The ColumnFields parameter corresponds to the Columns area. The PageFields parameter corresponds to the Filter area.
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 a pivot table, there are many settings you should control instead of letting Excel’s IntelliSense decide. For example, say that you are building a report with revenue that you will likely want to sum. If you don’t explicitly specify the calculation, Excel scans through the values in the underlying data. If 100% 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.
Although you are setting up the Data field, you can change several other properties within the same With…End With block. For example, 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.
Note
You cannot reuse the word Revenue as a name. Instead, you should add a trailing space after the word Revenue.
You are not required to specify a number format, but doing so 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 WithYour 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 set Subtotals(1) to True and then to False, like this:
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) = FalseAt this point, you have a complete pivot table like the one shown in Figure 12-3.
FIGURE 12-3 Running 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 this pivot table.
LISTING 12-1 Code to generate the pivot table shown in Figure 12-3
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.Create( _
SourceType:= xlDatabase, _
SourceData:=Prange.Address, _
Version:=xlPivotTableVersion14)
' Create the pivot table from the pivot cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Set up the row and 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
'Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"
With PT
.ColumnGrand = False
.RowGrand = False
.RepeatAllLabels xlRepeatLabels
End With
PT.PivotFields("Region").Subtotals(1) = True
PT.PivotFields("Region").Subtotals(1) = False
WSD.Activate
Range("J2").Select
End SubLearning 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 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 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 four rows below the current pivot table. At that point in the code, your worksheet looks as shown in Figure 12-5. The table in J2 is a live pivot table, and the table in J12 is 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.Create( _
SourceType:= xlDatabase, _
SourceData:=Prange.Address, _
Version:=xlPivotTableVersion14)
' Create the pivot table from the pivot cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' 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
' 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
' Delete the original pivot table and the pivot cache
PT.TableRange2.Clear
Set PTCache = Nothing
WSD.Activate
Range("J12").Select
End SubThe 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 very simple pivot table reports. Pivot tables offer far more flexibility, though. The sections that follow present more complex reporting examples.
Using advanced pivot table features
In this section, you use the detailed transactional data to 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:
Group the daily dates up to yearly dates.
Add multiple fields to the Values area.
Control the sort order so the largest customers are listed first.
Use the ShowPages feature to replicate the report for each product line manager.
After producing the pivot tables, convert each pivot table to values and do some basic formatting.
Figure 12-6 shows the report for one product line manager to give you an idea of 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: # 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 today, the Data field appears as Σ Values in the PivotTable Fields 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 a pivot table in VBA, you have two column 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. When you are creating a pivot table for someone to use, you should know that the fields in PageFields allow for easy ad hoc analysis. In this case, the value in PageFields 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 Note
This is a count of the number of records. It is not a count of the distinct values in a field. This kind of count was previously difficult to do in a pivot table. It is now possible using the Data Model. See the “Using the Data Model in Excel” section later in this chapter for details.
Grouping daily dates to months, quarters, or years
Pivot tables have the amazing capability to group daily dates up to months, quarters, and years. In VBA, this feature is a bit annoying because you must select a date cell before issuing the grouping command.
Note
I used to go through all sorts of gyrations to figure out where the first date field was. In fact, you can simply refer to PT.PivotFields("Date").LabelRange to point to the Date heading.
There are seven choices for grouping times or dates: Seconds, Minutes, Hours, Days, Months, Quarters, and Years. Note that you can group a field by multiple items. To do so, you specify a series of 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) Note
Never choose to group by only months without including years. If you do this, Excel combines January from all years in the data into a single item called January. Although this is great for seasonality analyses, it is rarely what you want in a summary. Always choose Years and Months in the Grouping dialog box.
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, January 1, 2018, to Sunday, January 2, 2020, use this code:
With PT.PivotFields("Date")
.LabelRange.Group _
Start:=DateSerial(2018, 1, 1), _
End:=DateSerial(2020, 1, 4), _
By:=7, _
Periods:=Array(False, False, False, True, False, False, False)
On Error Resume Next
.PivotItems("<1/1/2018").Visible = False
.PivotItems(">1/2/2020").Visible = False
On Error Goto 0
End With Note
There is one limitation to grouping by week. When you group by week, you cannot also group by any other measure. For example, grouping by both week and quarter is not valid.
Excel 2016 briefly introduced the concept of AutoGrouping for dates. Excel 2016 had built-in rules that analyzed the span of dates and decided whether dates should be grouped by month or by month, quarter, and year. It was generally hated and removed by Excel 2019. But, the VBA still exists:
PT.AutoGroup
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)
Tip
Before grouping the daily dates up to years, you had about 500 date columns across this report. After grouping, you have two date columns plus a total. I prefer to group the dates as soon as possible in the macro. If you added the other two data fields to the report before grouping, your report would be 1,500 columns wide. Although this is not a problem since Excel 2007 increased the column limit from 256 to 16,384, it still creates an unusually large report when you ultimately need only a few columns. Allowing the pivot table to grow to 1,500 columns, even for a few lines of code, would make the worksheet’s last cell be column BER.
After you group daily dates to years, the new Year field is still called Date. This might not always be the case. 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 PivotTable Fields list to hold years.
Changing the calculation to show percentages
Excel offers 15 choices on the Show Values As tab of the Value Field Settings dialog box. These calculations enable you to change how a field is displayed in the report. Instead of showing sales, you could show sales as a percentage of 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. If you need a running total along a date column, use this:
' Set up Running Total .Calculation = xlRunningTotal .BaseField = "Date"
With ship months going down the rows, 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 WithNote that with positional calculations, you cannot use the AutoShow or AutoSort methods. This is too bad; it would be interesting to sort the customers from high to low and 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 WithTable 12-1 shows the complete list of .Calculation options. The second column indicates the compatibility of the calculation with earlier versions of Excel. The third column indicates whether you need a base field or a base item.
TABLE 12-1 Complete list of .Calculation options
Calculation | Version compatibility | BaseField/BaseItem |
|---|---|---|
| All | Both required |
| All | Neither |
| All | Both required |
| All | Both required |
| All | Neither |
| 2010+ |
|
| 2010+ | Both required |
| 2010+ | Both required |
| All | Neither |
| All | Neither |
| 2010+ |
|
| 2010+ |
|
| 2010+ |
|
| All |
|
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 Note
Take careful note of the name of the first field in the preceding code. By default, Excel would use Sum of Revenue. If you think this is a goofy title (as I do), you can change it. However, you cannot change it to Revenue because there is already a field in the PivotTable Fields list with that name.
In the preceding code, I use the name Revenue with a trailing space. This works fine, and no one notices the extra space. However, in the rest of the macro, when you refer to this field, remember to refer to it as Revenue with a trailing space.
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 for this 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"
Note
Although the proper code is to set this value to a text zero, Excel puts a real zero in the empty cells.
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 menu 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 replicates your pivot table for every item in one of the fields in the Filters area.
Because you built the report in this example with Product as a filter field, it takes only the following code to replicate the pivot table for every product:
' Replicate the pivot table for each product PT.ShowPages PageField:="Product"
After running this code, you have a new worksheet for every product in the data set. From there, you have some simple formatting and calculations to do. Check the end of the macro, shown in Listing 12-3, for these techniques, which should be second nature by this point in the book.
LISTING 12-3 Code to produce one 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.Create( _
SourceType:= xlDatabase, _
SourceData:=Prange.Address, _
Version:=xlPivotTableVersion14)
' Create the pivot table from the pivot cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' 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
' 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
' 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 SubFiltering a data set
There are many ways to filter a pivot table, from using the slicers, to the conceptual filters, to simply selecting and clearing items from one of the many field drop-down menus.
Manually filtering two or more items in a pivot field
When you open a field heading drop-down menu and select or clear items from the list, you are applying a manual filter (see Figure 12-8).
For example, say that 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
FIGURE 12-8 This filter drop-down menu offers manual filters, a search box, and conceptual filters.
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 PivItemUsing the conceptual filters
Excel 2007 introduced conceptual filters for date fields, numeric fields, and text fields. Open the drop-down menu for any field label in the pivot table, and 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").ClearAllFiltersTo apply a date filter to the date field to find records from this week, use this code:
PT.PivotFields("Date").PivotFilters.Add Type:=xlThisWeekThe 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:=100000Other value filters might enable you to specify, for example, 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:=100000Table 12-2 lists all the possible filter types.
TABLE 12-2 Filter types
Filter type | Description |
|---|---|
| Filters for all dates before a specified date. |
| Filters for all dates on or before a specified date. |
| Filters for all dates after a specified date. |
| Filters for all dates on or after a specified date. |
| Filters for all dates in January. |
| Filters for all dates in February. |
| Filters for all dates in March. |
| Filters for all dates in April. |
| Filters for all dates in May. |
| Filters for all dates in June. |
| Filters for all dates in July. |
| Filters for all dates in August. |
| Filters for all dates in September. |
| Filters for all dates in October. |
| Filters for all dates in November. |
| Filters for all dates in December. |
| Filters for all dates in Quarter 1. |
| Filters for all dates in Quarter 2. |
| Filters for all dates in Quarter 3. |
| Filters for all dates in Quarter 4. |
| Filters for the specified number of values from the bottom of a list. |
| Filters for the specified percentage of values from the bottom of a list. |
| Sums the values from the bottom of the list. |
| Filters for all captions, beginning with the specified string. |
| Filters for all captions that contain the specified string. |
| Filters for all captions that do not begin with the specified string. |
| Filters for all captions that do not contain the specified string. |
| Filters for all captions that do not end with the specified string. |
| Filters for all captions that do not match the specified string. |
| Filters for all captions that end with the specified string. |
| Filters for all captions that match the specified string. |
| Filters for all captions that are within a specified range of values. |
| Filters for all captions that are greater than the specified value. |
| Filters for all captions that are greater than or match the specified value. |
| Filters for all captions that are less than the specified value. |
| Filters for all captions that are less than or match the specified value. |
| Filters for all captions that are not within a specified range of values. |
| Filters for all dates that are within a specified range of dates. |
| Filters for all dates that apply to the previous month. |
| Filters for all dates that apply to the previous quarter. |
| Filters for all dates that apply to the previous week. |
| Filters for all dates that apply to the previous year. |
| Filters for all dates that apply to the next month. |
| Filters for all dates that apply to the next quarter. |
| Filters for all dates that apply to the next week. |
| Filters for all dates that apply to the next year. |
| Filters for all dates that apply to the current month. |
| Filters for all dates that apply to the current quarter. |
| Filters for all dates that apply to the current week. |
| Filters for all dates that apply to the current year. |
| Filters for all dates that apply to the current date. |
| Filters for all dates that apply to the next day. |
| Filters for all dates that apply to the previous day. |
| Filters for all dates that do not match a specified date. |
| Filters for all dates that match a specified date. |
| Filters for the specified number of values from the top of a list. |
| Filters for the specified percentage of values from the top of a list. |
| Sums the values from the top of the list. |
| Filters for all values that do not match the specified value. |
| Filters for all values that match the specified value. |
| Filters for all values that are within a specified range of values. |
| Filters for all values that are greater than the specified value. |
| Filters for all values that are greater than or match the specified value. |
| Filters for all values that are less than the specified value. |
| Filters for all values that are less than or match the specified value. |
| Filters for all values that are not within a specified range of values. |
| Filters for all values that are within one year of a specified date. |
Using the search filter
Excel 2010 added a Search box to the filter drop-down menu. Although this is a slick feature in the Excel interface, there is no equivalent magic in VBA. Whereas the drop-down menu offers the Select All Search Results check box, the equivalent VBA just lists all the items that match the selection. To achieve the same results in VBA, use the xlCaptionContains filter described in the code that precedes Table 12-2.
FIGURE 12-10 The Top 5 Customers report contains two pivot tables.
The Top 5 Customers report actually contains two snapshots of a pivot table. After using the AutoShow feature to grab the top five markets with their totals, the macro went back to the pivot table, removed the AutoShow option, and grabbed the total of all customers to produce the Total Company row.
Setting up slicers to filter a pivot table
Excel 2010 introduced the concept of slicers for filtering pivot tables. A slicer is a visual filter that you can resize and reposition. You can control the color of a slicer and control the number of columns in it. You can also select or unselect items from a slicer by using VBA.
Figure 12-11 shows a pivot table with two slicers. Both of the slicers have been modified to show multiple columns.
FIGURE 12-11 Slicers provide a visual filter of several fields.
Slicers work only with pivot tables designed to be used by Excel 2010 or newer. 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. The following code would set up a slicer:
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 by using height and width in points. Specify the location by 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)Every slicer starts out as one column. You can change the style and number of columns code like this:
' Format the color and number of columns With SLP .Style = "SlicerStyleLight6" .NumberOfColumns = 5 End With
After the slicer is defined, you can 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 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 WithListing 12-4 shows how to build a pivot table with two slicers.
LISTING 12-4 Code to build a pivot table with two slicers
Sub PivotWithTwoSlicers()
Dim SCP As SlicerCache ' For Product slicer
Dim SCC As SlicerCache ' For Customer slicer
Dim SLP As Slicer
Dim SLC 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(18, FinalCol + 2), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15)
' Set up the row and column fields
PT.AddFields RowFields:=Array("Region")
' Set up the data fields
With PT.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = "Quantity "
End With
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "$#,##0"
.Name = "Revenue "
End With
With PT.PivotFields("Profit")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "$#,##0"
.Name = "Profit "
End With
' Define the slicer caches
Set SCC = WBD.SlicerCaches.Add(PT, "Customer")
Set SCP = WBD.SlicerCaches.Add(PT, "Product")
' Define Product as a slicer
Set SLP = SCP.Slicers.Add(WSD, , _
Name:="Product", _
Caption:="Product", _
Top:=WSD.Range("J14").Top + 5, _
Left:=WSD.Range("J14").Left + 5, _
Width:=343, Height:=54)
SLP.Style = "SlicerStyleLight4"
SLP.NumberOfColumns = 5
' Define Customer as a slicer
Set SLC = SCC.Slicers.Add(WSD, , _
Name:="Customer", _
Caption:="Customer", _
Top:=WSD.Range("J1").Top + 5, _
Left:=WSD.Range("J1").Left + 5, _
Width:=415, Height:=184)
SLC.Style = "SlicerStyleLight2"
SLC.NumberOfColumns = 3
' Unselect some products
With SCP
.SlicerItems("C409").Selected = False
.SlicerItems("D625").Selected = False
.SlicerItems("E438").Selected = False
End With
' Unselect one customer
With SCC
.SlicerItems("Guarded Kettle Corporation").Selected = False
End With
End SubThe preceding code assigned the newly created slicer to an object variable so you could easily format the slicer. What if a slicer was created before your macro starts running? You can easily figure out the name of the slicer. If a slicer is created for the Product field, for example, 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 SubSetting up a timeline to filter an Excel pivot table
Microsoft introduced the Timeline slicer in Excel 2013. This is a special type of slicer that is not compatible with Excel 2010 or earlier. The marketing name of Excel 2013 was Version 15, and VBA still uses that name, so if you plan on using a Timeline slicer, you have to specify xlPivotTableVersion15 (or higher) in two places in the code:
' 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.Add2(PT, "ShipDate", , _ SlicerCacheType:=xlTimeline)
Then you add the slicer 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 a timeline, use the TimelineViewState.Level property:
SL.TimelineViewState.Level = xlTimelineLevelYears
To filter a 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-5 shows the complete macro to build a version 15 pivot table and add a Timeline slicer.
LISTING 12-5 Code to build a pivot with a 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)
' Set up the row and 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
' Define the slicer cache
' First two arguments are Source and SourceField
' Third argument, Name, should be skipped
Set SC = WBD.SlicerCaches.Add2(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/2018", "12/31/2018"
End SubFigure 12-12 shows the Timeline slicer built by the code in Listing 12-5.
FIGURE 12-12 Timelines were introduced in Excel 2013.
Formatting the intersection of values in a pivot table
Microsoft 365 allows you to assign a formatting rule to the intersection of labels in a pivot table. Let’s say you have a pivot table with Sector in rows and Region in columns. If you add a fill color to the cell at the intersection of the Financial sector and the East region, the fill color will follow Financial and East as the pivot table is rearranged. If you add a new inner row field, such as Product, after Sector, then all of the product cells for the Financial sector will have the same fill color.
There is nothing particularly magical about the VBA to set this up. If the intersection of Financial and East is in B7, you would use the following:
With Range("B7").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = -0.249946592608417
.PatternTintAndShade = 0
End WithUsing the Data Model in Excel
Excel incorporates most parts of Power Pivot into the core Excel product. This means you can add two tables to the Data Model, create a relationship, build a measure, 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:
Add the main table to the Data Model.
Add the lookup table to the Data Model.
Link the two tables with a relationship.
Create a pivot cache from
ThisWorkbookDataModel.Create a pivot table from the cache.
Add row fields.
Define a measure. Add the measure to the pivot table.
Adding both tables to the Data Model
You should already have a data set 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 this code that use the table name, the workbook path, or the workbook name. By storing the table name in a variable at the top of the code, you can use the variables to build the connection name, connection string, and command text.
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. 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 pivot cache 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. To set up the pivot cache, use this code:
' 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 two types of fields you need to add to the pivot table. Text fields such as Customer, Sector, and 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, let’s look at 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 WithIn the current example, add the Sector field from the Sector table by using this code:
With PT.CubeFields("[Sector].[Sector]")
.Orientation = xlRowField
.Position = 1
End WithAdding numeric fields to the Values area
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 Power Pivot window.) In VBA, you need 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 the 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 code to create a new measure. The following measure uses the new xlDistinctCount 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"
Now that PowerPivot ships with every copy of Excel, you can use DAX formulas to create new measures. The following code adds a field for Median Sales:
' Add Median Sales using DAX
ActiveWorkbook.Model.ModelMeasures.Add _
MeasureName:="Median Sales", _
AssociatedTable:=ActiveWorkbook.Model.ModelTables("Sales"), _
Formula:="Median([Revenue])", _
FormatInformation:=ActiveWorkbook.Model.ModelFormatCurrency("Default", 2)
PT.AddDataField PT.CubeFields("[Measures].[Median Sales]")Putting it all together
Figure 12-13 shows the Data Model pivot table created using the code in Listing 12-6.
FIGURE 12-13 Two tables are linked with a pivot table and two measures via a macro.
LISTING 12-6 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.Add2 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.Add2 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 the 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("[Measures].[Sum of 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"
' Add Median Sales using DAX
ActiveWorkbook.Model.ModelMeasures.Add _
MeasureName:="Median Sales", _
AssociatedTable:= _
ActiveWorkbook.Model.ModelTables("Sales"), _
Formula:="Median([Revenue])", _
FormatInformation:= _
ActiveWorkbook.Model.ModelFormatCurrency("Default", 2)
PT.AddDataField PT.CubeFields("[Measures].[Median Sales]")
End SubUsing 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 more useful type creates 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, as shown here:
' 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 Note
If you create a field called Profit Percent, the default pivot table produces a field called Sum of Profit Percent. This title is misleading and downright silly. To prevent this, use the Name property when defining the Data field to replace Sum of Profit Percent with something such as GP Pct. Keep in mind that this name must differ from the name for the calculated field.
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 minus 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 record set
When you double-click any number in any pivot table in the Excel user interface, 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 data set.
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 on the Design tab contains four drop-down menus 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 and 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 ColumnGrand when you want to suppress the total row because Microsoft calls that row the “grand total for columns.” Get it? In other words, Microsoft is saying that the row at the bottom contains the total of the columns above it. It is one of the more awkward phrases in the Excel ribbon. It confuses me every time.
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” by using 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 = TrueSuppressing 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 data set 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 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) = FalseComparing VBA to TypeScript
There are billions of lines of VBA code running in Excel worksheets around the world. But Microsoft says that VBA will never be supported in Excel Online. They have created a new macro language to be used in Excel Online. Currently, the TypeScript language available for macros in Excel Online will not run in Desktop versions of Excel. It is much slower than VBA and not as complete as VBA.
However, it does exist, and you can create some interesting macros, including macros to create pivot tables in Excel Online.
Currently, TypeScript is limited to Microsoft 365 customers with a commercial or educational license. That excludes everyone with a Home license. In addition, you will need your Microsoft 365 administrator to enable them for the company. For the latest rules, open a browser and search for “Introduction to Office Scripts in Excel.”
At the time of writing, the TypeScript macros are still in beta. TypeScript and the macro recorder can successfully record these actions:
Add a new pivot table to the right of your data set.
Add Product to the Rows area.
Add Revenue to the Values area.
Add Region to the Columns area.
The recorded macro is short, relatively easy to understand, and runs successfully.
On the downside, the macro recorder does not (yet) support:
Changing the number format of a numeric field.
Replacing blanks in the Values area with zero.
Using the suggested pivot tables from the Ideas feature.
Figure 12-14 shows the recorded TypeScript code.
FIGURE 12-14 A 13-line macro adds Product to the Rows, Revenue to Values, and Region to Columns in a new pivot table in Excel Online.
Here is a description of the recorded code with suggested changes:
The script is named Script 4. In the Script Details, click on the name and type a better name, such as CreatePTBySectorAndRegion.
Comment lines start with // instead of an apostophe in VBA.
It appears that selectSheet is an object variable. Where VBA would do:
Set selectedSheet = ActiveSheet Typescript does: let selectedSheet = workbook.getActiveWorksheet()
The code is case sensitive. You have to use getActiveWorksheet and not getactiveworksheet or GetActiveWorksheet. If you don’t use the case correctly, the editor will flag it as an error but then suggest that you could change the spelling to getActiveWorksheet. As a VBA fan, you will likely agree that it is a bit jerkish for the editor to know the spelling and to not automatically correct it.
When the macro recorder creates the pivot table using .addPivotTable, it specifies a name, source data, and destination data. Hard-coding A1:L564 is not a good practice. TypeScript’s equivalent of .CurrentRegion is .getSurroundingRegion() used as follows:
selectedSheet.getRange("A1").getSurroundingRegion()The modified code to create a pivot table called PTOne, based on A:L and starting in cell N2, is shown here. Note that the results are stored in an object variable called newPivotTable for easy reference later in the script:
let newPivotTable = workbook.addPivotTable("PTOne",
selectedSheet.getRange("A1").getSurroundingRegion(),
selectedSheet.getRange("N2"));Once the pivot table is created, you can add fields to the Rows area with the following:
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Sector"));For a column field, change .addRowHierarchy to .addColumnHierarchy. For a Values field, use .AddDataHierarchy.
Near the end of the script, the macro recorder added a stray line:
newPivotTable.getColumnHierarchy("Region").setPosition(0);This would have been needed if you had multiple fields in the Columns area. But with only one field in the Columns area, that line can be deleted.
Documentation for everything shows up when you hover over any word in the script. This is better than VBA, where you have to press F1 and then wait for a web page to load.
After seeing the recorded code, it should be relatively easy to edit the recorded code. The following code in Listing 12-7 creates a pivot table with Sector in rows, Region in columns, and Revenue in values.
LISTING 12-7 TypeScript code to create a pivot table in Excel Online
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Add a new pivot table on selectedSheet
let newPivotTable = workbook.addPivotTable("PTOne", selectedSheet.getRange("A1").
getSurroundingRegion(), selectedSheet.getRange("N2"));
// Add Sector to Rows
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Sector"));
// Add Revenue to Data
newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Revenue"));
// Add Region to Columns
newPivotTable.addColumnHierarchy(newPivotTable.getHierarchy("Region"));
}The resulting pivot table is shown in Figure 12-15.
FIGURE 12-15 A pivot table created in Excel Online from a TypeScript macro.
People who care a lot about speed note that the TypeScript macros are slower than VBA, but for this simple example, it does not matter.
There are still gaps. When you look at Figure 12-15, you might want to change “For Empty Cells, Show” to zero. There is a setting in Excel Online for this. But when you try to record a macro for how to make that change, the macro recorder tells you that this is not yet supported in TypeScript:
// Unsupported event received: PivotTable.nullStringChanged
I am writing this in 2021. Within a few years, these gaps will eventually be filled.
Next steps
You may be able to tell that 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. Chapter 13, “Excel power,” offers multiple techniques for handling various tasks in VBA.