Chapter 13
Using VBA to create pivot tables
In this chapter, you will:
Version 5 of Excel introduced a powerful new macro language called Visual Basic for Applications (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets. VBA enables you to perform steps that you normally perform in Excel quickly and flawlessly. I have seen a VBA program change a process that would take days each month into a single-click operation that now takes a minute of processing time.
Do not be intimidated by VBA. The VBA macro recorder tool gets you 90 percent of the way to a useful macro, and I get you the rest of the way, using examples in this chapter.
Enabling VBA in your copy of Excel
By default, VBA is disabled in Office 2019. Before you can start using VBA, you need to enable macros in the Trust Center. Follow these steps:
Click the File menu to show the Backstage view.
In the left navigation pane, select Options. The Excel Options dialog box displays.
In the left navigation pane of Excel Options, select Customize Ribbon.
In the list box on the right, choose the Developer tab from the list of main tabs available in Excel. Click OK to close Excel Options and include the Developer tab in the ribbon.
Click the Developer tab in the ribbon. As shown in Figure 13-1, the Code group on the left side of the ribbon includes icons for the Visual Basic Editor, Macros, Macro Recorder, and Macro Security.
Click the Macro Security icon. Excel opens the Trust Center.
In the Trust Center, choose one of the four options:
Disable All Macros with Notification—When you open a workbook that contains macros, a message appears alerting you that macros are in the workbook. If you expect macros to be in the workbook, you can enable the macros. This is the safest setting because it forces you to explicitly enable macros in each workbook.
Enable All Macros—This setting is not recommended because potentially dangerous code can run. Because it can enable rogue macros to run in files that are sent to you by others, Microsoft recommends that you not use this setting.
Disable All Macros Without Notification—Your macros will not be able to run and, as the option says, you will not be notified that they’re not running. You don’t want to choose this option.
Disable All Macros Except Digitally Signed Macros—You would have to buy a digital code signing certificate from a third party in order to use this option. This is a waste of money if you are building macros for you and your co-workers.
Using a file format that enables macros
The default Excel 2019 file format is initially the Excel workbook (.xlsx). This workbook is defined to disallow macros. You can build a macro in an .xlsx workbook, but it won’t be saved with the workbook.
You have several options for saving workbooks that enable macros:
Excel Macro-Enabled Workbook (.xlsm)—This uses the XML-based method for storing workbooks and enables macros. I prefer this file type because it is compact and less prone to becoming corrupt.
Excel Binary Workbook (.xlsb)—This is a binary format and always enables macros.
Excel 97-2003 Workbook (.xls)—Although this legacy file type supports macros, it doesn’t support a lot of handy newer features. You lose access to slicers, new filters, rows 65537 through 1048576, columns IW through XFD, and other pivot table improvements.
When you create a new workbook, you can use File, Save As to choose the appropriate file type.
Visual Basic Editor
From Excel, press Alt+F11 or select Developer, Visual Basic to open the Visual Basic Editor, as shown in Figure 13-2. The three main sections of the VBA Editor are described here. If this is your first time using VBA, some of these items might be disabled. Follow the instructions in the following list to make sure that each is enabled:
Project Explorer—This pane displays a hierarchical tree of all open workbooks. Expand the tree to see the worksheets, code modules, user forms, and class modules present in the workbook. If the Project Explorer is not visible, enable it by pressing Ctrl+R.
Properties window—The Properties window is important when you begin to program user forms. It has some use when you are writing normal code, so enable it by pressing F4.
Code window—This is the area where you write your code. Code is stored in one or more code modules attached to your workbook. To add a code module to a workbook, select Insert, Module from the VBA menu.
Visual Basic tools
Visual Basic is a powerful development environment. Although this chapter cannot offer a complete course on VBA, if you are new to VBA, you should take advantage of these important tools:
AutoComplete—As you begin to type code, Excel might offer a drop-down menu with valid choices. This feature, known as AutoComplete, enables you to type code faster and eliminate typing mistakes.
Excel Help—For assistance on any keyword, put the cursor in the keyword and press F1. Excel Help displays a help topic regarding the keyword.
Comments—Excel checks each line of code as you finish it. Lines in error appear in red. Comments appear in green. You can add a comment by typing a single apostrophe before the comment. Use lots of comments so you can remember what each section of code is doing.
Debugging—Despite the aforementioned error checking, Excel might still encounter an error at runtime. If this happens, click the Debug button. The line that caused the error is highlighted in yellow. Hover your mouse cursor over any variable to see the current value of the variable. When you are in Debug mode, use the Debug menu to step line by line through code. If you have a wide monitor, try arranging the Excel window and the VBA window side by side. This way, you can see the effect of running a line of code on the worksheet.
Other great debugging tools are breakpoints, the Watch window, the Object Browser, and the Immediate window. Read about these tools in the Excel Help menu.
The macro recorder
Excel offers a macro recorder that is about 90 percent perfect. Unfortunately, the last 10 percent is frustrating. Code that you record to work with one data set is hard-coded to work only with that data set. This behavior might work fine if your transactional database occupies cells A1:L87601 every single day, but if you are pulling in a new invoice register every day, it is unlikely that you will have the same number of rows each day. Given that you might need to work with other data, it would be a lot better if Excel could record selecting cells using the End key. This is one of the shortcomings of the macro recorder.
In reality, Excel pros use the macro recorder to record code but then expect to have to clean up the recorded code.
Understanding object-oriented code
VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax. However, in VBA, it is called Object.Method. Examples of objects are workbooks, worksheets, cells, and ranges of cells. Methods can be typical Excel actions such as .Copy, .Paste, and .PasteSpecial.
Many methods allow adverbs—parameters you use to specify how to perform the method. If you see a construct with a colon and an equal sign (:=), you know that the macro recorder is describing how the method should work.
You also might see code in which you assign a value to the adjectives of an object. In VBA, adjectives are called properties. If you set ActiveCell.Value = 3, you are setting the value of the active cell to 3. Note that when you are dealing with properties, there is only an = (equal sign), not a := (colon and equal sign).
Learning tricks of the trade
This section explains a few simple techniques you need to master in order to write efficient VBA code. These techniques help you make the jump to writing effective code.
Writing code to handle a data range of any size
The macro recorder hard-codes the fact that your data is in a range, such as A1:L87601. Although this hard-coding works for today’s data set, it might not work as you get new data sets. You need to write code that can deal with data sets of different sizes.
The macro recorder uses syntax such as Range("H12") to refer to a cell. However, it is more flexible to use Cells(12, 8) to refer to the cell in row 12, column 8. Similarly, the macro recorder refers to a rectangular range as Range("A1:L87601"). However, it is more flexible to use the Cells syntax to refer to the upper-left corner of the range and then use the Resize() syntax to refer to the number of rows and columns in the range. The equivalent way to describe the preceding range is Cells(1, 1).Resize(87601,12). This approach is more flexible because you can replace any of the numbers with a variable.
In the Excel user interface, you can use Ctrl+any arrow on the keyboard to jump to the edge of a range of data. If you move the cell pointer to the final row on the worksheet and press the Ctrl+up arrow key, the cell pointer jumps to the last row with data. The equivalent of doing this in VBA is to use the following code:
Range("A1048576").End(xlUp).Select
Caution
The arguments for the End property are XLUP, XLDOWN, XLTOLEFT, and XLTORIGHT. Using these properties is equivalent to pressing Ctrl plus the up, down, left, or right arrow keys. Since the VBA Editor shows XLUP as xlUp, many people think the argument contains the number one instead of the letter L. Think of how “XL” sounds like “Excel.” There is also no logical explanation for why Microsoft added the word To in XLToLeft and XLToRight.
You do not need to select this cell; you just need to find the row number that contains the last row. The following code locates this row and saves the row number to a variable named FinalRow:
FinalRow = Range("A1048576").End(xlUp).Row
There is nothing magical about the variable name FinalRow. You could call this variable x, y, or even your dog’s name. However, because VBA enables you to use meaningful variable names, you should use something such as FinalRow to describe the final row:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Note
Excel 2019 offers 1,048,576 rows and 16,384 columns for a regular workbook. If the workbook opens in Compatibility mode, you have only 65,536 rows and 256 columns. To make your code flexible enough to handle either situation, you can use Rows.Count to learn the total number of rows in the current workbook. The preceding code can then be generalized like so:
FinalRow = Cells(Rows.Count, 1).End(x1Up).Row
You can also find the final column in a data set. If you are relatively sure that the data set begins in row 1, you can use the End key in combination with the left-arrow key to jump from cell XFD1 to the last column with data. To generalize for the possibility that the code is running in legacy versions of Excel, you can use the following code:
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
Using super-variables: Object variables
In typical programming languages, a variable holds a single value. You might use x = 4 to assign the value 4 to the variable x.
Think about a single cell in Excel. Many properties describe a cell. A cell might contain a value such as 4, but the cell also has a font size, a font color, a row, a column, possibly a formula, possibly a comment, a list of precedents, and more. It is possible in VBA to create a super-variable that contains all the information about a cell or about any object. A statement to create a typical variable such as x = Range("A1") assigns the current value of A1 to the variable x.
However, you can use the Set keyword to create an object variable:
Set x = Range("A1")
You have now created a super-variable that contains all the properties of the cell. Instead of having a variable with only one value, you have a variable in which you can access the values of many properties associated with that variable. You can reference x.Formula to learn the formula in A1 or x.Font.ColorIndex to learn the color of the cell.
Note
The examples in this chapter frequently set up an object variable called PT to refer to the entire pivot table. This way, any time the code would generally refer to ActiveSheet.PivotTables("PivotTable1"), you can specify PT to avoid typing the longer text.
Using With and End With to shorten code
You will frequently find that you repeatedly make certain changes to a pivot table. Although the following code is explained later in this chapter, all these lines of code are for changing settings in a pivot table:
PT.NullString = 0
PT.RepeatAllLabels xlRepeatLabels
PT.ColumnGrand = False
PT.RowGrand = False
PT.RowAxisLayout xlTabularRow
PT.TableStyle2 = "PivotStyleMedium10"
PT.TableStyleRowStripes = True
For all these lines of code, the VBA engine has to figure out what you mean by PT. Your code executes faster if you refer to PT only once. Add the initial line With PT. Then all the remaining lines do not need to start with PT. Any line that starts with a period is assumed to be referring to the object in the With statement. Finish the code block by using an End With statement:
With PT
.NullString = 0
.RepeatAllLabels xlRepeatLabels
.ColumnGrand = False
.RowGrand = False
.RowAxisLayout xlTabularRow
.TableStyle2 = "PivotStyleMedium10"
.TableStyleRowStripes = True
End With
Understanding versions
Pivot tables have been evolving. They were introduced in Excel 5 and perfected in Excel 97. In Excel 2000, pivot table creation in VBA was dramatically altered. Some new parameters were added in Excel 2002. A few new properties, such as PivotFilters and TableStyle2, were added in Excel 2007. Slicers and new choices for Show Values As were added in Excel 2010. Timelines and the Power Pivot Data Model were added in Excel 2013. The AutoGroup method was added in Excel 2019. Because of all the changes over the years, you need to be extremely careful when writing code in Excel 2019 that might be run in older versions of Excel.
Each of the last four versions of Excel offered many new features in pivot tables. If you use code for a new feature, the code works in the current version, but it crashes in previous versions of Excel:
Excel 2016 introduced the AutoGroup functionality for dates in pivot tables. To perform the equivalent action in VBA, use the
AutoGroupmethod.Excel 2013 introduced the Power Pivot Data Model. You can add tables to the Data Model, create a relationship, and produce a pivot table. This code does not run in Excel 2010 or earlier. The function
xlDistinctCountwas new in Excel 2013, as were timelines.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 or earlier.Excel 2007 introduced
ConvertToFormulas,xlCompactRowlayout,xlAtTopfor the subtotal location,TableStyles, andSortUsingCustomLists. Macros that include this code fail in previous versions.
Building a pivot table in Excel VBA
This chapter does not mean to imply that you 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
This chapter’s code listings are available for download at microsoftpressstore.com/Excel2019PivotTable/downloads.
Caution
Beginning with Excel 2007, the user interface has new names for the various sections of a pivot table. Even so, VBA code continues to refer to the old names. Although the four sections of a pivot table in the Excel user interface are Filters, Columns, Rows, and Values, VBA continues to use the old terms of page fields, column fields, row fields, and data fields. If Microsoft hadn’t made this decision, millions of lines of code would have stopped working in Excel 2007 when they referred to a page field instead of a filter field.
In Excel 2000 and newer, you first build a pivot cache object to describe the input area of the data:
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
After defining the pivot cache, use the CreatePivotTable method to create a blank pivot table based on the defined pivot cache:
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, _
FinalCol + 2), TableName:="PivotTable1")
In the CreatePivotTable method, you specify the output location and optionally give the table a name. After running this line of code, you have a strange-looking blank pivot table, like the one shown in Figure 13-3. You now have to use code to drop fields onto the table.
CreatePivotTable method, Excel gives you a four-cell blank pivot table that is not useful. Caution
If you choose the Defer Layout Update setting in the user interface to build the pivot table, Excel does not recalculate the pivot table after you drop each field onto the table. By default, in VBA, Excel calculates the pivot table as you execute each step of building the table. This could require the pivot table to be executed a half-dozen times before you get to the final result.
To speed up your code execution, you can temporarily turn off calculation of the pivot table by using the ManualUpdate property:
PT.ManualUpdate = True
You can now run through the steps needed to lay out the pivot table. In the AddFields method, you can specify one or more fields that should be in the row, column, or filter area of the pivot table.
The RowFields parameter enables you to define fields that appear in the Rows area of the PivotTable Fields list. The ColumnFields parameter corresponds to the Columns layout area. The PageFields parameter corresponds to the Filters layout area.
The following line of code populates a pivot table with two fields in the Rows area and one field in the Columns area:
' Set up the row & column fields
PT.AddFields RowFields:=Array("Category", "Product"), _
ColumnFields:="Region"
Note
If you are adding a single field such as Region to the Columns area, you only need to specify the name of the field in quotes. If you are adding two or more fields, you have to include that list inside the array function.
Although the row, column, and filter fields of the pivot table can be handled with the AddFields method, it is best to add fields to the data area using the code described in the next section.
Adding fields to the data area
When you are adding fields to the data area of a pivot table, for many settings it is better for you to have control than to let Excel’s IntelliSense decide.
Say that you are building a report with revenue. You likely want to sum the revenue. If you do not explicitly specify the calculation, Excel scans through the values in the underlying data. If 100 percent of the revenue cells are numeric, Excel sums. If one cell is blank or contains text, Excel decides to count the revenue. This produces confusing results.
Because of this possible variability, you should never use the DataFields argument in the AddFields method. Instead, change the property of the field to xlDataField. You can then specify the function to be xlSum.
While you are setting up the data field, you can change several other properties within the same With...End With block.
The Position property is useful when 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 something strange like Sum Of Revenue. You can use the Name property to change that heading back to something normal. Note that you cannot reuse the word Revenue as a name, but you can use "Revenue " (with a trailing space).
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 With
The preceding block of code adds the Revenue field to the Values area of the pivot table with a new name and a number format.
Formatting the pivot table
Microsoft introduced the Compact layout for pivot tables in Excel 2007. This means that three layouts are available in Excel 2019 (Compact, Tabular, and Outline). When a pivot table is created with VBA, Excel usually defaults to using the Tabular layout, which is good because Tabular view is the one that makes the most sense. It cannot hurt, though, to add one line of code to ensure that you get the desired layout:
PT.RowAxisLayout xlTabularRow
In Tabular layout, each field in the row area is in a different column. Subtotals always appear at the bottom of each group. This is the layout that has been around the longest and is most conducive to reusing a pivot table report for further analysis.
The Excel user interface frequently defaults to Compact layout. In this layout, multiple column fields are stacked up into a single column on the left side of the pivot table. To create this layout, use the following code:
PT.RowAxisLayout xlCompactRow
Tip
The one limitation of Tabular layout is that you cannot show the totals at the top of each group. If you need to do this, you’ll want to switch to the Outline layout and show totals at the top of the group:
PT.RowAxisLayout xlOutlineRow
PT.SubtotalLocation xlAtTop
Your pivot table inherits the table style settings selected as the default on whatever computer happens to run the code. If you would like 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"
At this point, you have given VBA all the settings required to correctly generate the pivot table. If you set ManualUpdate to False, Excel calculates and draws the pivot table. Thereafter, you can immediately set this back to True by using this code:
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
At this point, you have a complete pivot table, like the one shown in Figure 13-4.
Listing 13-1 shows the complete code used to generate this pivot table.
LISTING 13-1 Code to generate the pivot table shown in Figure 13-4
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("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("N1:AZ1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Category", "Product"), _
ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With
' Format the pivot table
PT.RowAxisLayout xlTabularRow
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
Cells(2, FinalCol + 2).Select
End Sub
Dealing with limitations of pivot tables
As with pivot tables in the user interface, Microsoft maintains tight control over a live pivot table. You need to be aware of these issues as your code is running on a sheet with a live pivot table.
Filling blank cells in the data area
It is a bit annoying that Excel puts blank cells in the data area of a pivot table. For example, in Figure 13-4, the Midwest region had no sales of a Speed Rail 8 Quart/Liter, so that cell (P15) appears blank instead of containing a zero.
You can override this in the Excel interface by using the For Empty Cells Show setting in the PivotTable Options dialog box. The equivalent code is shown here:
PT.NullString = "0"
Note
Note that the Excel macro recorder always wraps that zero in quotation marks. No matter whether you specify "0" or just 0, the blank cells in the data area of the pivot table have numeric zeros.
Filling blank cells in the row area
Excel 2010 added a much-needed setting to fill in the blank cells along the left columns of a pivot table. This problem happens any time that you have two or more fields in the row area of a pivot table. Rather than repeat a label such as “Bar Equipment” in cells N5:N18 in the pivot table shown in Figure 13-4, Microsoft traditionally has left those cells blank. To solve this problem in Excel 2019, use the following line of code:
PT.RepeatAllLabels xlRepeatLabels
Preventing errors from inserting or deleting cells
You cannot use many Excel commands inside a pivot table. Inserting rows, deleting rows, and cutting and pasting parts of a pivot table are all against the rules.
Say that you tried to delete the Grand Total column from column W in a pivot table. If you try to delete or clear column W, the macro comes to a screeching halt with a 1004 error, as shown in Figure 13-5.
There are two strategies for getting around this limitation. The first strategy is to find if there is already an equivalent command in the pivot table interface. For example, you want to determine whether there is code to perform any of these actions:
Remove the grand total column.
Remove the grand total row.
Add blank rows between each section.
Suppress subtotals for outer row fields.
The second strategy is to convert the pivot table to values. You can then insert, cut, and clear as necessary.
Both strategies are discussed in the following sections.
Controlling totals
The default pivot table includes a grand total row and a grand total column. You can choose to hide one or both of these elements.
To remove the grand total column from the right side of the pivot table, use this:
PT.RowGrand = False
To remove the grand total row from the bottom of the pivot table, use this:
PT.ColumnGrand = False
Turning off the subtotals rows is surprisingly complex. This issue comes up when you have multiple fields in the row area. Excel automatically turns on subtotals for the outermost row fields.
Tip
Did you know that you can have a pivot table show multiple subtotal rows? I have never seen anyone actually do this, but you can use the Field Settings dialog box to specify that you want to see Sum, Average, Count, Max, Min, and so on. Figure 13-6 shows the Custom setting where you set this.
To suppress the subtotals for a field, you must set the Subtotals property equal to an array of 12 False values. The first False turns off automatic subtotals, the second False turns off the Sum subtotal, the third False turns off the Count subtotal, and so on. This line of code suppresses the Category subtotal:
PT.PivotFields("Category").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 all subtotals are suppressed:
PT.PivotFields("Category").Subtotals(1) = True
PT.PivotFields("Category").Subtotals(1) = False
Note
You might be wondering about the Distinct Count option introduced in Excel 2013. Does it force a twelfth position in the array? No. The Custom subtotals option is grayed out for pivot tables that use the Data Model, so you won’t ever be able to choose Sum and Distinct Count together.
See “Using the Data Model in Excel 2019,” later in this chapter for an example of using Distinct Count.
Converting a pivot table to values
If you plan on converting a live pivot table to values, you need to copy the entire pivot table. How much space it will take might be tough to predict. If you summarize transactional data every day, you might find that on any given day, you do not have sales from one region. This can cause your table to be perhaps seven columns wide on some days and only six columns wide on other days.
Excel provides two range properties that you can use to refer to a pivot table. The TableRange2 property includes all the rows of the pivot table, including any Filter drop-down menus at the top of the pivot table. The TableRange1 property starts just below the filter fields. It often includes the unnecessary row with Sum Of Revenue at the top of the pivot table.
If your goal is to convert a pivot table to values and not move the pivot table to a new place, you can use this code:
PT.TableRange2.Copy
PT.TableRange2.PasteSpecial xlPasteValues
If you want to copy only the data section of the pivot table to a new location, you frequently use the Offset property to start one row lower than the top of TableRange2, like so:
PT.TableRange2.Offset(1,0).Copy
This reference copies the data area plus one row of headings.
Notice in Figure 13-7 that using Offset without .Resize causes one extra row to be copied. However, because that row is always blank, there is no need to use Resize to not copy the extra blank row.
The code copies PT.TableRange2 and uses PasteSpecial on a cell six rows below the current pivot table.
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 13-2 uses a pivot table to produce a summary from the underlying data. More than 80,000 rows are reduced to a tight 50-row summary. The resulting data is properly formatted for additional filtering, sorting, and so on. At the end of the code, the pivot table is copied to static values, and the pivot table is cleared.
LISTING 13-2 Code to produce a static summary from a pivot table
Sub UsePivotToCreateValues()
'
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("N1:AZ1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Region", "Category"), _
ColumnFields:="Data"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
With PT.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = "#,##0"
.Name = "COGS"
End With
' Settings to create a solid block of data
With PT
.NullString = 0
.RepeatAllLabels Repeat:=xlRepeatLabels
.ColumnGrand = False
.RowGrand = 0
.PivotFields("Region").Subtotals(1) = True
.PivotFields("Region").Subtotals(1) = False
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' Copy the pivot table as values below the pivot table
PT.TableRange2.Offset(1, 0).Copy
PT.TableRange1.Cells(1, 1).Offset(PT.TableRange1.Rows.Count + 4, 0). _
StartRow = PT.TableRange1.Cells(1, 1).Offset( _
PT.TableRange1.Rows.Count+ 5, 0).Row
' Figure 13.7 at this point
PT.TableRange1.Clear
Set PTCache = Nothing
WSD.Activate
Cells(StartRow, FinalCol + 2).Select
End Sub
The code in Listing 13-2 creates the pivot table. It then copies the results as values and pastes them below the original pivot table. In reality, you probably want to copy this report to another worksheet or another workbook. Examples later in this chapter introduce the code necessary for this.
So far, this chapter has walked you through building the simplest of pivot table reports. Pivot tables offer far more flexibility, though. Read on for more complex reporting examples.
Pivot table 201: Creating a report showing revenue by category
A typical report might provide a list of markets by category with revenue by year. This report could be given to product line managers to show them which markets are selling well. The report in Figure 13-8 is not a pivot table, but the macro to create the report used a pivot table to summarize the data. Regular Excel commands such as Subtotal then finish off the report.
In this example, you want to show the markets in descending order by revenue, with years going across the columns. A sample pivot table report is shown in Figure 13-9.
There are some tricky issues involved in creating this pivot table:
You have to roll the daily dates in the original data set up to years.
You want to control the sort order of the row fields.
You want to fill in blanks throughout the pivot table, use a better number format, and suppress the subtotals for the Category field.
The key to producing this data quickly is to use a pivot table. The default pivot table has a number of quirky problems that you can correct in the macro. To start, use VBA to build a pivot table with Category and Region as the row fields. Add Date as a column field. Add Revenue as a data field. Here’s the code to do all this:
PT.AddFields RowFields:=Array("Category", _
"Region"), ColumnFields:="Date"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With
Figure 13-10 shows the default pivot table created with these settings.
Here are just a few of the annoyances that most pivot tables present in their default state:
The outline view is horrible. In Figure 13-10, the value Bar Equipment appears in the product column only once and is followed by six blank cells. Thankfully, Excel 2019 offers the
RepeatAllLabelsmethod to correct this problem. If you intend to repurpose the data, you need the row labels to be repeated on every row.Because the original data set contains daily dates, the default pivot table has more than 1,000 columns of daily data. No one is able to process this report. You need to roll those daily dates up to years. Pivot tables make this easy.
The report contains blank cells instead of zeros. In Figure 13-10, the entire visible range of Bar Equipment is blank. These cells should contain zeros instead of blanks.
The title is boring. Most people would agree that Sum Of Revenue is an annoying title.
Some captions are extraneous. Date floating in cell P2 of Figure 13-10 does not belong in a report.
The default alphabetical sort order is rarely useful. Product line managers are going to want the top markets at the top of the list. It would be helpful to have the report sorted in descending order by revenue.
The borders are ugly. Excel draws in myriad borders that make the report look awful.
Pivot tables offer no intelligent page break logic. If you want to produce one report for each product line manager, there is no fast method for indicating that each product should be on a new page.
Because of the page break problem, you might find that it is easier to do away with the pivot table’s subtotal rows and have the
Subtotalmethod add subtotal rows with page breaks. You need a way to turn off the pivot table subtotal rows offered for Category in Figure 13-10. These rows show up automatically whenever you have two or more row fields. If you had four row fields, you would want to turn off the automatic subtotals for the three outermost row fields.
Even with all these problems in default pivot tables, default pivot tables are still the way to go. You can overcome each complaint either by using special settings within the pivot table or by entering a few lines of code after the pivot table is created and then copied to a regular data set.
Ensuring that Tabular layout is utilized
In legacy versions of Excel, multiple row fields appeared in multiple columns. Three layouts are now available. The Compact layout squeezes all the row fields into a single column. Compact layout is the default when a pivot table is created in the Excel interface. Currently, when you create a pivot table in VBA, the default is the Tabular layout. However, in some future version, Microsoft will correct this discrepancy, so get in the habit of explicitly changing the layout to a Tabular layout with this code:
PT.RowAxisLayout xlTabularRow
Rolling daily dates up to years
With transactional data, you often find your date-based summaries having one row per day. Although daily data might be useful to a plant manager, many people in the company want to see totals by month or quarter and year.
The great news is that Excel handles the summarization of dates in a pivot table with ease. If you have ever had to use the arcane formula =A2+1-Day(A2) to change daily dates into monthly dates, you will appreciate the ease with which you can group transactional data into months or quarters.
Tip
Although the Excel 2019 user interface automatically groups daily dates up to months, quarters, and years, pivot tables created with VBA do not automatically group the dates. If you are sure that your code will never have to run in Excel 2013 or earlier, you could use the following code to automatically group dates as in the Excel 2019 interface:
PT.PivotFields("Date").AutoGroup
After experiencing the AutoGroup feature for three years, it is quirky enough to avoid. For example, if your data includes January 1 and December 31 of a year, the AutoGroup will include year in the grouping. But if you did not invoice on January 1 or December 31 due to the holiday, then the AutoGroup will exclude years. It is safer to use the following legacy code.
Creating a date group with VBA is a bit quirky. The Group method can be applied to only a single cell in the pivot table, and that cell must contain a date or the Date field label.
In Figure 13-10, you would have to select either the Date heading in cell P2 or one of the dates in cells P3:APM3. Selecting one of these specific cells is risky, particularly if the pivot table later starts being created in a new column. Two other options are more reliable.
First, if you will never use a different number of row fields, then you can assume that the Date heading is in row 1, column 3 of the area known as TableRange2. The following line of code selects this cell:
PT.TableRange2.Cells(1, 3).Select
You should probably add a comment that you need to edit the 3 in that line to another number any time that you change the number of row fields.
Another solution is to use the LabelRange property for the Date field. The following code always selects the cell containing the Date heading:
PT.PivotFields("Date").LabelRange.Select
To group the daily dates up to yearly dates, you should define a pivot table with Date in the row field. Turn off ManualUpdate to enable the pivot table to be drawn. You can then use the LabelRange property to locate the date label.
You use the Group method on the date label cell. You specify an array of seven Boolean values for the Periods argument. The seven values correspond to seconds, minutes, hours, days, months, quarters, and years. For example, to group by years, you would use this:
PT.PivotFields("Date"),LabelRange.Group _
Periods:=(False, False, False, False, False, False, True)
After you have grouped by years, the field is still called Date. This differs from the results when you group by multiple fields. To group by months, quarters, and years, you would use this:
PT.PivotFields("Date"),LabelRange.Group _
Periods:=(False, False, False, False, True, True, True)
After you have grouped up to months, quarters, and years, the Date field starts referring to months. Two new virtual fields are available in the pivot table: Quarters and Years.
To group by weeks, you choose only the Day period and then use the By argument to group into seven-day periods:
PT.PivotFields("Date"),LabelRange.Group By:=7_
Periods:=(False, False, False, True, False, False, False)
In Figure 13-10, the goal is to group the daily dates up to years, so the following code is used:
PT.PivotFields("Date"),LabelRange.Group _
Periods:=(False, False, False, False, False, False, True)
Figure 13-11 shows the pivot table after grouping daily dates up to years.
Group method.Eliminating blank cells
The blank cells in a pivot table are annoying. You will want to fix two kinds of blank cells. Blank cells occur in the Values area when there were no records for a particular combination. For example, in Figure 13-11, the company did not sell bar equipment in 2022, so all of cells P4:P11 are blank. Most people would prefer to have zeros instead of those blank cells.
Blank cells also occur in the Row Labels area when you have multiple row fields. The words Bar Equipment appear in cell N4, but then cells N5:N10 are blank.
To replace blanks in the Values area with zeros, use this:
PT.NullString = "0"
Note
Although the preceding code appears to use a zero inside of quotation marks, Excel actually puts a numeric zero in the empty cells.
To fill in the blanks in the label area in Excel 2019, use this:
PT.RepeatAllLabels xlRepeatLabels
The RepeatAllLabels code fails in Excel 2007 and earlier. The only solution in legacy versions of Excel is to convert the pivot table to values and then set the blank cells to a formula that grabs the value from the row above, like this:
Dim FillRange As Range
Set PT = ActiveSheet.PivotTables("PivotTable1")
' Locate outer row column
Set FillRange = PT.TableRange1.Resize(, 1)
' Convert entire table to values
PT.TableRange2.Copy
PT.TableRange2.PasteSpecial xlPasteValues
' Fill Special Cells Blanks with the value from above
FillRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=R[-1]C"
' Convert those formulas to values
FillRange.Value = FillRange.Value
Controlling the sort order with AutoSort
The Excel user interface offers an AutoSort option that enables you to sort a field in descending order based on revenue. The equivalent code in VBA to sort the region and category fields by descending revenue uses the AutoSort method:
PT.PivotFields("Region").AutoSort Order:=xlDescending, _
Field:="Sum of Revenue"
PT.PivotFields("Category").AutoSort Order:=xlDescending, _
Field:="Sum of Revenue"
Changing the default number format
Numbers in the Values area of a pivot table need to have a suitable number format applied. You cannot count on the numeric format of the underlying field carrying over to the pivot table.
To show the Revenue values with zero decimal places and a comma, use this:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0"
Some companies have customers who typically buy thousands or millions of dollars’ worth of goods. You can display numbers in thousands by using a single comma after the number format. To do this, you would include a K abbreviation to indicate that the numbers are in thousands:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"
Local custom dictates the thousands abbreviation. If you are working for a relatively young computer company where everyone uses K for the thousands separator, you are in luck because Microsoft makes it easy to use this abbreviation. However, if you work at a more than 100-year-old soap company where you use M for thousands and MM for millions, you have a few more hurdles to jump. You must prefix the M character with a backslash to have it work:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,\M"
Alternatively, you can surround the M character with double quotation marks. To put double quotation marks inside a quoted string in VBA, you must use two sequential quotation marks. To set up a format in tenths of millions that uses the #,##0.0,,"MM" format, you would use this line of code:
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0.0,,""M"""
Here, the format is quotation mark, pound, comma, pound, pound, zero, period, zero, comma, comma, quotation mark, quotation mark, M, quotation mark, quotation mark, quotation mark. The three quotation marks at the end are correct. You use two quotation marks to simulate typing one quotation mark in the custom number format box and a final quotation mark to close the string in VBA.
Figure 13-12 shows the pivot table blanks filled in, numbers shown in thousands, and category and region sorted in descending order.
Suppressing subtotals for multiple row fields
As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. That extra row field can get in the way if you plan on reusing the results of the pivot table as a new data set for some other purpose.
In the current example, you have taken 80,000 rows of data and produced a tight 50-row summary of yearly sales by category and region. That new data set would be interesting for sorting, filtering, and charting if you could remove the total row and the category subtotals.
To remove the subtotal, you first set the Subtotals(1) property to True to turn off the other 10 possible subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:
PT.PivotFields("Category").Subtotals(1) = True
PT.PivotFields("Category").Subtotals(1) = False
To remove the grand total row, use this:
PT.ColumnGrand = False
Figure 13-13 shows the first section of the pivot table with the subtotals removed.
Copying a finished pivot table as values to a new workbook
If you plan to repurpose the results of a pivot table, you need to convert the table to values. This section shows you how to copy a pivot table to a brand-new workbook.
To make the code more portable, assign object variables to the original workbook, new workbook, and first worksheet in the new workbook. At the top of the procedure, add these statements:
Dim WSR As Worksheet
Dim WSD As Worksheet
Dim WBO As Workbook
Dim WBN As Workbook
Set WBO = ActiveWorkbook
Set WSD = Worksheets("Data")
After the pivot table has been successfully created, build a blank report workbook with this code:
' Create a New Blank Workbook with one Worksheet
Set WBN = Workbooks.Add(xlWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
' Set up Title for Report
With WSR.Range("A1")
.Value = "Revenue by Category, Region and Year"
.Style = "Title"
End With
There are a few remaining annoyances in the pivot table. The borders are annoying, and there are stray labels such as Sum Of Revenue and Date in the first row of the pivot table. You can solve these problems by excluding the first row(s) of PT.TableRange2 from the Copy method and then using PasteSpecial(xlPasteValuesAndNumberFormats) to copy the data to the report sheet.
In the current example, the TableRange2 property includes only one row to eliminate, row 2, as shown in Figure 13-13. If you had a more complex pivot table with several column fields and/or one or more page fields, you would have to eliminate more than just the first row of the report. It helps to run your macro to this point, look at the result, and figure out how many rows you need to delete. You can effectively not copy these rows to the report by using the Offset property. Then copy the TableRange2 property, offset by one row.
Purists will note that this code copies one extra blank row from below the pivot table, but this really does not matter because the row is blank. After copying, you can erase the original pivot table and destroy the pivot cache, like this:
' Copy the Pivot Table data to row 3 of the Report sheet
' Use Offset to eliminate the title row of the pivot table
PT.TableRange2.Offset(1, 0).Copy
WSR. Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
PT.TableRange1.Clear
Set PTCache = Nothing
Tip
Note that you use the Paste Special option to paste just values and number formats. This gets rid of both borders and the pivot nature of the table. You might be tempted to use the All Except Borders option under Paste, but that keeps the data in a pivot table, and you will not be able to insert new rows in the middle of the data.
Handling final formatting
The last steps for the report involve some basic formatting tasks and addition of the subtotals. You can bold and right-justify the headings in row 3. Set up rows 1–3 so that the top three rows print on each page:
' Do some basic formatting
' Autofit columns, format the headings , right-align
Range("A3").EntireRow.Style = "Heading 4"
Range("A3").CurrentRegion.Columns.AutoFit
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3:B3").HorizontalAlignment = xlLeft
' Repeat rows 1-3 at the top of each page
WSR.PageSetup.PrintTitleRows = "$1:$3"
Adding subtotals to get page breaks
The Data tab offers a powerful feature: subtotals. Figure 13-14 shows the Subtotal dialog box. Note the option Page Break Between Groups. Rather than looping through records to manually add a page break after each category, you can apply them in one command using the Subtotal method.
If you were sure that you would always have three years and a total, you could use the following code to add subtotals for each line of business group:
' Add Subtotals by Category.
' Be sure to add a page break at each change in category
Selection.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(3, 4, 5, 6), PageBreaks:=True
However, this code fails if you have more or less than three years. The solution is to use the following convoluted code to dynamically build a list of the columns to total, based on the number of columns in the report:
Dim TotColumns()
Dim I as Integer
FinalCol = Cells(3, Columns.Count).End(xlToLeft).Column
ReDim Preserve TotColumns(1 To FinalCol - 2)
For i = 3 To FinalCol
TotColumns(i - 2) = i
Next i
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotColumns,_
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
Finally, with the new totals added to the report, you need to AutoFit the numeric columns again with this code:
Dim GrandRow as Long
' Make sure the columns are wide enough for totals
GrandRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(3, 3).Resize(GrandRow - 2, FinalCol - 2).Columns.AutoFit
Cells(GrandRow, 3).Resize(1, FinalCol - 2).NumberFormat = "#,##0,K"
' Add a page break before the Grand Total row, otherwise
' the manager for the final category will have two totals
WSR.HPageBreaks.Add Before:=Cells(GrandRow, 1)
Putting it all together
Listing 13-3 produces the product line manager reports in a few seconds. Figure 13-15 shows the report produced by this code.
LISTING 13-3 Code that produces the category report in Figure 13-15
Sub CategoryRegionReport()
' Category and Region as Row
' Years as Column
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim TotColumns()
Set WSD = Worksheets("Data")
Dim WSR As Worksheet
Dim WBO As Workbook
Dim WBN As Workbook
Set WBO = ActiveWorkbook
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("N1:XFD1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:=Array("Category", _
"Region"), ColumnFields:="Date"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With
' Ensure tabular layout is used
PT.RowAxisLayout xlTabularRow
' Calc the pivot table before grouping dates
PT.ManualUpdate = False
PT.ManualUpdate = True
PT.PivotFields("Date").LabelRange.Group _
Periods:=Array(False, False, False, False, False, False, True)
' Change number format of Revenue
PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"
' Fill in blank cells
PT.NullString = "0"
PT.RepeatAllLabels xlRepeatLabels
' Sort both label fields by descending revenue
PT.PivotFields("Category").AutoSort Order:=xlDescending, _
field:="Sum of Revenue"
PT.PivotFields("Region").AutoSort Order:=xlDescending, _
field:="Sum of Revenue"
' Suppress Category totals
PT.PivotFields("Category").Subtotals(1) = True
PT.PivotFields("Category").Subtotals(1) = False
PT.ColumnGrand = False
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' Create a New Blank Workbook with one Worksheet
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
' Set up Title for Report
With WSR.[A1]
.Value = "Revenue by Category & Region"
.Style = "Title"
End With
' Copy the Pivot Table data to row 3 of the Report sheet
' Use Offset to eliminate the title row of the pivot table
PT.TableRange1.Offset(1, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
PT.TableRange2.Clear
Set PTCache = Nothing
' Do some basic formatting
' Autofit columns, bold the headings, right-align
Range("A3").EntireRow.Style = "Heading 4"
Range("A3").CurrentRegion.Columns.AutoFit
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3:B3").HorizontalAlignment = xlLeft
' Repeat rows 1-3 at the top of each page
WSR.PageSetup.PrintTitleRows = "$1:$3"
' Add subtotals
FinalCol = Cells(3, 255).End(xlToLeft).Column
ReDim Preserve TotColumns(1 To FinalCol - 2)
For i = 3 To FinalCol
TotColumns(i - 2) = i
Next i
Range("A3").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=TotColumns, Replace:=True, _
PageBreaks:=True, SummaryBelowData:=True
' Make sure the columns are wide enough for totals
GrandRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(3, 3).Resize(GrandRow - 2, FinalCol - 2).Columns.AutoFit
Cells(GrandRow, 3).Resize(1, FinalCol - 2).NumberFormat = "#,##0,K"
' Add a page break before the Grand Total row, otherwise
' the product manager for the final Line will have two totals
WSR.HPageBreaks.Add Before:=Cells(GrandRow, 1)
End Sub
You have now seen the VBA code to produce useful summary reports from transactional data. The next section deals with additional features in pivot tables.
Calculating with a pivot table
So far in this chapter, the pivot tables have presented a single field in the Values area, and that field has always shown as a Sum calculation. You can add more fields to the Values area. You can change from Sum to any of 11 functions or alter the Sum calculation to display running totals, percentage of total, and more. You can also add new calculated fields or calculated items to the pivot table.
Addressing issues with two or more data fields
It is possible to have multiple fields in the Values section of a pivot report. For example, you might have Quantity, Revenue, and Cost in the same pivot table.
When you have two or more data fields in an Excel 2019 pivot table that you built in the Excel interface, the value fields go across the columns. However, VBA builds the pivot table with the Values fields going down the innermost row field. This creates a bizarre-looking table like the one shown in Figure 13-16.
To correct this problem, you should specify that a virtual field called Data is one of the column fields.
Note
In this instance, note that Data is not a column in your original data; it is a special name used to indicate the orientation of the multiple Values fields.
To have multiple Values fields go across the report, use this code:
PT.AddFields RowFields:="State", ColumnFields:="Data"
After adding a column field called Data, you then define multiple data fields:
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0.00"
End With
With PT.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = "#,##0.00"
End With
With PT.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "#,##0"
End With
This code produces the pivot table shown in Figure 13-17.
Using calculations other than Sum
So far, all the pivot tables in this chapter have used the Sum function to calculate. There are 11 functions available, including Sum. To specify a different calculation, specify one of these values as the Function property:
xlAverage—Average
xlCount—Count
xlCountNums—Count numeric values only
xlMax—Maximum
xlMin—Minimum
xlProduct—Multiply
xlStDev—Standard deviation, based on a sample
xlStDevP—Standard deviation, based on the whole population
xlSum—Sum
xlVar—Variation, based on a sample
xlVarP—Variation, based on the whole population
Although Count Distinct was added in Excel 2013, you cannot create Count Distinct in a regular pivot-cache pivot table. See “Using the Data Model in Excel 2019” at the end of this chapter.
Tip
Note that when you add a field to the Values area of the pivot table, Excel modifies the field name with the function name and the word of. For example, “Revenue” becomes “Sum Of Revenue.” “Cost” might become “StdDev Of Cost.” If you later need to refer to those fields in your code, you need to do so using the new name, such as Average Of Quantity.
You can improve the look of your pivot table by changing the Name property of the field. If you do not want Sum Of Revenue appearing in the pivot table, change the Caption property to something like Total Revenue. This sounds less awkward than Sum Of Revenue. Remember that you cannot have a name that exactly matches an existing field name in the pivot table, so “Revenue” is not suitable as a name. However, “Revenue” (with a trailing space) is fine to use as a name.
For text fields, the only function that makes sense is a count. You will frequently count the number of records by adding a text field to the pivot table and using the Count function.
The following code fragment calculates total revenue, a count of records by counting a text field, and average quantity:
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "$#,##0.00"
.Name = " Revenue"
End With
With PT.PivotFields("Customer")
.Orientation = xlDataField
.Function = xlCount
.Position = 2
.NumberFormat = "#,##0"
.Name = "# of Records"
End With
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlAverage
.Position = 3
.NumberFormat = "#,##0.00"
.Name = "Average Revenue"
End With
' Ensure that we get zeros instead of blanks in the data area
PT.NullString = "0"
PT.TableStyle2 = "PivotStyleMedium3"
Figure 13-18 shows the pivot table this code creates.
Using calculated data fields
Pivot tables offer two types of formulas. The most useful type defines a formula for a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level.
To set up a calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula, like so:
PT.CalculatedFields.Add Name:="GrossProfit", Formula:="=Revenue-Cost"
PT.CalculatedFields.Add "GP_Pct", "=GrossProfit/Revenue"
After you define the field, add it as a data field:
With PT.PivotFields("GrossProfit")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "$#,##0"
.Caption = "Gross Profit"
End With
With PT.PivotFields("GP_Pct")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
.NumberFormat = "0.0%"
.Caption = "GP%"
End With
Figure 13-19 shows the Gross Profit calculated field.
A calculated field can be referenced in subsequent calculated fields. The following code uses the Gross Profit field to calculate Gross Profit Percent. Although the Caption property renamed the field to “Gross Profit” (with a space in the middle), the field name in the preceding code is “GrossProfit” (without a space). Use the field name in the following calculation:
PT.CalculatedFields.Add "GP_Pct", "=GrossProfit/Revenue", True
With PT.PivotFields("GP_Pct")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
.NumberFormat = "0.0%"
.Caption = "GP%"
End With
Figure 13-20 shows a report with GP%.
Using calculated items
Calculated items have the potential to produce incorrect results in a pivot table. Say that you have a report of sales by nine states. You want to show a subtotal of four of the states. A calculated item would add a ninth item to the state column. Although the pivot table gladly calculates this new item, it causes the grand total to appear overstated.
Figure 13-21 shows a pivot table with these nine states. The total revenue is $10 million. When a calculated item provides a subtotal of four states (see Figure 13-22), the grand total increases to $15 million. This means that the items that make up the calculated item are included in the total twice. If you like restating numbers to the Securities and Exchange Commission, feel free to use calculated items.
The code to produce the calculated item is shown here. Calculated items are added as the final position along the field, so this code changes the Position property to move the DesertStates item to the proper position:
PT.PivotFields("State").CalculatedItems.Add _
Name:="DesertStates", _
Formula:="=California +Nevada +Arizona +'New Mexico'"
PT.PivotFields("State").PivotItems("California").Position = 1
PT.PivotFields("State").PivotItems("Nevada").Position = 2
PT.PivotFields("State").PivotItems("Arizona").Position = 3
PT.PivotFields("State").PivotItems("New Mexico").Position = 4
PT.PivotFields("State").PivotItems("DesertStates").Position = 5
If you hope to use a calculated item, you should either remove the grand total row or remove the four states that go into the calculated item. This code hides the four states, and the resulting pivot table returns to the correct total, as shown in Figure 13-23:
PT.PivotFields("State").CalculatedItems.Add _
Name:="DesertStates", _
Formula:="=California +Nevada +Arizona +'New Mexico'"
' Hide the items included in the new subtotal
With PT.PivotFields("State")
.PivotItems("California").Visible = False
.PivotItems("Nevada").Visible = False
.PivotItems("Arizona").Visible = False
.PivotItems("New Mexico").Visible = False
End With
A better solution, which is discussed in the next section, is to skip calculated items and use text grouping.
Calculating groups
If you need to calculate subtotals for certain regions, a better solution is to use text grouping to define the groups. If you group the four states, Excel adds a new field to the row area of the pivot table. Although this process requires some special handling, it is worthwhile and creates a nice-looking report.
To group four states in the Excel interface, you select the cells that contain those four states and select Group Selection from the PivotTable Tools Options tab. This immediately does several things:
The items in the group are moved together in the row area.
A new field is added to the left of the state field. If the original field was called State, the new field is called State2.
Annoyingly, the subtotals property for the new State2 field is set to None instead of Automatic.
A subtotal for the selected items is added with the name of Group1.
Any items that are not in a group have a new subtotal added to State2 with the state name repeated.
In VBA, it is somewhat tricky to select the cells that contain the proper states. The following code uses the LabelRange property to point to the cells and then uses the Union method to refer to the four noncontiguous cells:
Set R1 = PT.PivotFields("State").PivotItems("California").LabelRange
Set R2 = PT.PivotFields("State").PivotItems("Arizona").LabelRange
Set R3 = PT.PivotFields("State").PivotItems("New Mexico").LabelRange
Set R4 = PT.PivotFields("State").PivotItems("Nevada").LabelRange
Union(R1, R2, R3, R4).Group
After setting up the first group, rename the newly created States2 field to have a suitable name:
PT.PivotFields("State2").Caption = "State Group"
Then change the name of this region from Group1 to the desired group name:
PT.PivotFields("State Group").PivotItems("Group1").Caption = "Desert States"
Change the Subtotals property from None to Automatic:
PT.PivotFields("State Group").Subtotals(1) = True
After you have set up the first group, you can define the remaining groups with this code:
Set R1 = PT.PivotFields("State").PivotItems("Utah").LabelRange
Set R2 = PT.PivotFields("State").PivotItems("Colorado").LabelRange
Union(R1, R2).Group
PT.PivotFields("State Group").PivotItems("Group2").Caption = "Rockies"
Set R1 = PT.PivotFields("State").PivotItems("Texas").LabelRange
Set R2 = PT.PivotFields("State").PivotItems("Louisiana").LabelRange
Set R3 = PT.PivotFields("State").PivotItems("Oklahoma").LabelRange
Union(R1, R2, R3).Group
PT.PivotFields("State Group").PivotItems("Group3").Caption = "Oil States"
The result is a pivot table with new virtual groups, as shown in Figure 13-24.
Using Show Values As to perform other calculations
The Show Values As tab in the Value Field Settings dialog box offers 15 different calculations. These calculations enable you to change from numbers to percentage of total, running totals, ranks, and more.
You change the calculation by using the Calculation option for the pivot field.
Note
The Calculation property works with the BaseField and BaseItem properties. Depending on the selected calculation, you might be required to specify a base field and base item, or sometimes only a base field, or sometimes neither of them.
Some calculations, such as % of Column and % of Row, need no further definition; you do not have to specify a base field. Here is code that shows revenue as a percentage of total revenue:
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Calculation = xlPercentOfTotal
.Position = 2
.NumberFormat = "0.0%"
.Name = "% of Total"
End With
Other calculations need a base field. If you are showing revenue and ask for the descending rank, you can specify that the base field is the State field. In this case, you are asking for this state’s rank based on revenue:
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Calculation = xlRankDescending
.BaseField = "State"
.Position = 3
.NumberFormat = "0%"
.Name = "RankD"
End With
A few calculations require both a base field and a base item. If you want to show every state’s revenue as a percentage of California revenue, you have to specify % Of as the calculation, State as the base field, and California as the base item:
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Calculation = xlPercentOf
.BaseField = "State"
.BaseItem = "California"
.Position = 4
.NumberFormat = "0%"
.Name = "% of CA"
End With
Some of the calculation fields were new in Excel 2010. In Figure 13-25, column I uses the new % of Parent calculation and column H uses the old % of Total calculation. In both columns, Desert States is 52% of the Grand Total (cells H8 and I8). However, cell I5 shows that California is 60.8% of Desert States, whereas cell H5 shows that California is 31.6% of the grand total.
Notice that the Rank field is specified to use State as a base field. The states are ranked within each State Group. There does not appear to be a way to have the states ranked overall unless you remove the State Group column from the pivot table.
Table 13-1 shows the complete list of Calculation options. The second column indicates whether the calculations are compatible with previous versions of Excel. The third column indicates whether you need a base field and base item.
TABLE 13-1 Calculation options available in Excel 2019 VBA
Calculation |
Version |
Base Field/Base Item? |
|
All |
Both required |
|
All |
Neither |
|
All |
Neither |
|
All |
Both required |
|
All |
Both required |
|
All |
Neither |
|
2010 and later |
Base field only |
|
2010 and later |
Both required |
|
2010 and later |
Both required |
|
All |
Neither |
|
All |
Neither |
|
2010 and later |
Base field only |
|
2010 and later |
Base field only |
|
2010 and later |
Base field only |
|
All |
Base field only |
Using advanced pivot table techniques
Even if you are a pivot table pro, you might never have run into some of the really advanced techniques available with pivot tables. The following sections discuss such techniques.
Using AutoShow to produce executive overviews
If you are designing an executive dashboard utility, you might want to spotlight the top five markets. This setting lets you select either the top or bottom n records, based on any data field in the report.
The code to use AutoShow in VBA uses the AutoShow method:
' Show only the top 5 Markets
PT.PivotFields("Market").AutoShow Top:=xlAutomatic, Range:=xlTop, _
Count:=5, Field:= "Sum of Revenue"
When you create a report using the AutoShow method, it is often helpful to copy the data and then go back to the original pivot report to get the totals for all markets. In the code in Listing 13-4, this is achieved by removing the Market field from the pivot table and copying the grand total to the report. The code in Listing 13-4 produces the report shown in Figure 13-26.
LISTING 13-4 Code used to create the Top 5 Markets report
Sub Top5Markets()
' Produce a report of the top 5 markets
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("M1:Z1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Market", ColumnFields:="Category"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = "Total Revenue"
End With
' Ensure that we get zeros instead of blanks in the data area
PT.NullString = "0"
' Sort markets descending by sum of revenue
PT.PivotFields("Market").AutoSort Order:=xlDescending, _
field:="Total Revenue"
' Show only the top 5 markets
PT.PivotFields("Market").AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=5, field:="Total Revenue"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
' Create a new blank workbook with one worksheet
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
' Set up title for report
With WSR.[A1]
.Value = "Top 5 Markets"
.Font.Size = 14
End With
' Copy the pivot table data to row 3 of the report sheet
' Use offset to eliminate the title row of the pivot table
PT.TableRange2.Offset(1, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
LastRow = WSR.Cells(Rows.Count, 1).End(xlUp).Row
WSR.Cells(LastRow, 1).Value = "Top 5 Total"
' Go back to the pivot table to get totals without the AutoShow
PT.PivotFields("Market").Orientation = xlHidden
PT.ManualUpdate = False
PT.ManualUpdate = True
PT.TableRange2.Offset(2, 0).Copy
WSR.Cells(LastRow + 2, 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
' Clear the pivot table
PT.TableRange2.Clear
Set PTCache = Nothing
' Do some basic formatting
' Autofit columns, bold the headings, right-align
WSR.Range(WSR.Range("A3"), WSR.Cells(LastRow + 2, 9)).Columns.AutoFit
Range("A3").EntireRow.Font.Bold = True
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3").HorizontalAlignment = xlLeft
Range("A2").Select
MsgBox "CEO Report has been Created"
End Sub
The Top 5 Markets 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 goes back to the pivot table, removes the AutoShow option, and grabs the total of all markets to produce the Total Company row.
Using ShowDetail to filter a Recordset
Open any pivot table in the Excel user interface. Double-click any number in the pivot table. Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to perform a drill-down query into a data set.
The equivalent VBA property is ShowDetail. By setting this property to True for any cell in a pivot table, you generate a new worksheet with all the records that make up that cell:
PT.TableRange1.Offset(2, 1).Resize(1, 1).ShowDetail = True
Listing 13-5 produces a pivot table with the total revenue for the top three stores and ShowDetail for each of those stores. This is an alternative method to using the Advanced Filter report. The results of this macro are three new sheets. Figure 13-27 shows the first sheet created.
ShowDetail property to retrieve the records for each of those stores.LISTING 13-5 Code used to create a report for each of the top three customers
Sub RetrieveTop3CustomerDetail()
' Retrieve Details from Top 3 Customers
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("M1:Z1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Customer", ColumnFields:="Data"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = "Total Revenue"
End With
' Sort Stores descending by sum of revenue
PT.PivotFields("Customer").AutoSort Order:=xlDescending, _
field:="Total Revenue"
' Show only the top 3 stores
PT.PivotFields("Customer").AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=3, field:="Total Revenue"
' Ensure that we get zeros instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
' Produce summary reports for each customer
For i = 1 To 3
PT.TableRange2.Offset(i + 1, 1).Resize(1, 1).ShowDetail = True
' The active sheet has changed to the new detail report
' Add a title
Range("A1:A2").EntireRow.Insert
Range("A1").Value = "Detail for " & _
PT.TableRange2.Offset(i + 1, 0).Resize(1, 1).Value & _
" (Store Rank: " & i & ")"
Next i
MsgBox "Detail reports for top 3 stores have been created."
End Sub
Creating reports for each region or model
A pivot table can have one or more filter fields. A filter field goes in a separate set of rows above the pivot report. It can serve to filter the report to a certain region, certain model, or certain combination of region and model. In VBA, filter fields are called page fields.
You might create a pivot table with several filter fields to allow someone to do ad-hoc analyses. However, it is more likely that you will use the filter fields in order to produce reports for each region.
To set up a filter in VBA, add the PageFields parameter to the AddFields method. The following line of code creates a pivot table with Region in the Filters area:
PT.AddFields RowFields:= "Product", ColumnFields:= "Data", PageFields:= "Region"
The preceding line of code sets up the Region filter with the value (All), which returns all regions. To limit the report to just the North region, use the CurrentPage property:
PT.PivotFields("Region").CurrentPage = "North"
One use of a filter is to build a user form in which someone can select a particular region or particular product. You then use this information to set the CurrentPage property and display the results of the user form.
One amazing trick is to use the Show Pages feature to replicate a pivot table for every item in one filter field drop-down menu. After creating and formatting a pivot table, you can run this single line of code. If you have eight regions in the data set, eight new worksheets are inserted in the workbook, one for each region. The pivot table appears on each worksheet, with the appropriate region chosen from the drop-down menu:
PT.ShowPages PageField:=Region
Caution
Be careful with ShowPages. If you use ShowPages on the Customer field and you have 1,000 customers, Excel attempts to insert 1,000 worksheets in the workbook, each with a pivot table. All of those pivot tables share the same pivot cache in order to minimize memory usage. However, you will eventually run out of memory, and the program will end with a debug error when no additional worksheets will fit in available memory.
The other problem with ShowPages is that it creates the individual reports as worksheets in a single workbook. In real life, you probably want separate workbooks for each region so that you can email the reports to the appropriate office. You can loop through all PivotItems and display them one at a time in the page field. You can quickly produce top 10 reports for each region using this method.
To determine how many regions are available in the data, use PT.PivotFields("Region").PivotItems.Count. Either of these loops would work:
For i = 1 To PT.PivotFields("Region").PivotItems.Count
PT.PivotFields("Region").CurrentPage = _
PT.PivotFields("Region").PivotItems(i).Name
PT.ManualUpdate = False
PT.ManualUpdate = True
Next i
For Each PivItem In PT.PivotFields("Region").PivotItems
PT.PivotFields("Region").CurrentPage = PivItem.Name
PT.ManualUpdate = False
PT.ManualUpdate = True
Next PivItem
Of course, in both of these loops, the three region reports fly by too quickly to see. In practice, you would want to save each report while it is displayed.
So far in this chapter, you have been using PT.TableRange2 when copying the data from the pivot table. The TableRange2 property includes all rows of the pivot table, including the page fields.
There is also a TableRange1 property, which excludes the page fields. You can use either of these statements to get the detail rows:
PT.TableRange2.Offset(3, 0)
PT.TableRange1.Offset(1, 0)
Caution
Which statement you use is your preference, but if you use TableRange2, you will not have problems when you try to delete the pivot table with PT.TableRange2.Clear. If you were to accidentally attempt to clear TableRange1 when there are page fields, you would end up with the dreaded “Cannot move or change part of a pivot table” error.
Listing 13-6 produces a new workbook for each region, as shown in Figure 13-28.
LISTING 13-6 Code that creates a new worksheet for each region
Sub Top5ByRegionReport()
' Produce a report of top 5 customers for each region
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Data")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("M1:Z1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Customer", ColumnFields:="Data", _
PageFields:="Region"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0,K"
.Name = "Total Revenue"
End With
' Sort stores descending by sum of revenue
PT.PivotFields("Customer").AutoSort Order:=xlDescending, _
field:="Total Revenue"
' Show only the top 5 stores
PT.PivotFields("Customer").AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=5, field:="Total Revenue"
' Ensure that we get zeros instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
Ctr = 0
' Loop through each region
For Each PivItem In PT.PivotFields("Region").PivotItems
Ctr = Ctr + 1
PT.PivotFields("Region").CurrentPage = PivItem.Name
PT.ManualUpdate = False
PT.ManualUpdate = True
' Create a new blank workbook with one worksheet
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = PivItem.Name
' Set up Title for Report
With WSR.[A1]
.Value = "Top 5 Customers in the " & _
PivItem.Name & " Region"
End With
' Copy the pivot table data to row 3 of the report sheet
' Use offset to drop the page & title rows
PT.TableRange2.Offset(3, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
LastRow = WSR.Cells(65536, 1).End(xlUp).Row
WSR.Cells(LastRow, 1).Value = "Top 5 Total"
' Do some basic formatting
' Autofit columns, bold the headings, right-align
WSR.Range(WSR.Range("A2"), WSR.Cells(LastRow, 3)) _
.Columns.AutoFit
Range("A3").EntireRow.Font.Bold = True
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3").HorizontalAlignment = xlLeft
Range("B3").Value = "Revenue"
Range("A2").Select
Next PivItem
' Clear the pivot table
PT.TableRange2.Clear
Set PTCache = Nothing
MsgBox Ctr & " Region reports have been created"
End Sub
Manually filtering two or more items in a pivot field
In addition to setting up a calculated pivot item to display the total of a couple products that make up a dimension, you can manually filter a particular pivot field.
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. This is the code to hide a particular store:
PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False
Caution
You must be very careful never to set all items to False because doing so causes the macro to end with an error. This tends to happen more than you would expect. An application may first show products A and B and then on the next loop show products C and D. If you attempt to make A and B not visible before making C and D visible, no products will be visible along the pivot field, which causes an error. To correct this, always loop through all pivot items and make sure to turn them back to visible before the second pass through the loop.
This process is easy in VBA. After building the table with Product in the page field, loop through to change the Visible property to show only the total of certain products:
' Make sure all PivotItems along line are visible
For Each PivItem In _
PT.PivotFields("Product").PivotItems
PivItem.Visible = True
Next PivItem
' Now - loop through and keep only certain items visible
For Each PivItem In _
PT.PivotFields("Product").PivotItems
Select Case PivItem.Name
Case "Landscaping/Grounds Care", _
"Green Plants and Foliage Care"
PivItem.Visible = True
Case Else
PivItem.Visible = False
End Select
Next PivItem
Using the conceptual filters
Beginning with Excel 2007, conceptual filters for date fields, numeric fields, and text fields are provided. In the PivotTable Fields list, hover the mouse cursor over any active field in the field list portion of the pane. In the drop-down menu that appears, you can choose Label Filters, Date Filters, or Value Filters.
To apply a label filter in VBA, use the PivotFilters.Add method. The following code filters to the customers that start with 1:
PT.PivotFields("Customer").PivotFilters.Add _
Type:=xlCaptionBeginsWith, Value1:="1"
To clear the filter from the Customer field, use the ClearAllFilters method:
PT.PivotFields("Customer").ClearAllFilters
To apply a date filter to the date field to find records from this week, use this code:
PT.PivotFields("Date").PivotFilters.Add Type:=xlThisWeek
A value filter allows 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, you would use this code:
PT.PivotFields("Market").PivotFilters.Add _
Type:=xlValueIsGreaterThan, _
DataField:=PT.PivotFields("Sum of Revenue"), _
Value1:=100000
Other value filters might allow you to specify that you want branches where the revenue is between $50,000 and $100,000. In this case, you would specify one limit as Value1 and the second limit as Value2:
PT.PivotFields("Market").PivotFilters.Add _
Type:=xlValueIsBetween, _
DataField:=PT.PivotFields("Sum of Revenue"), _
Value1:=50000, Value2:=100000
Table 13-2 lists all the possible filter types.
TABLE 13-2 Filter types in VBA
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 between 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 between a specified range of values. |
|
Filters for all dates that are between 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. |
|
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 do not match the specified value. |
|
Filters for all values that match the specified value. |
|
Filters for all values that are between 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 between 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. Figure 13-29 shows the (Select All Search Results) check box selected after the search for “be.” Using the macro recorder during this process creates a 5,876-line macro that goes through and turns all customers without “be” to invisible:
xlCaptionContains filter.With ActiveSheet.PivotTables("PivotTable3").PivotFields("Customer")
.PivotItems("ACASCO Corp.").Visible = False
.PivotItems("ACECUL Corp.").Visible = False
.PivotItems("ACEHUA Corp.").Visible = False
' snipped 5870_ similar lines
.PivotItems("ZUQHYR Corp.").Visible = False
.PivotItems("ZUSOEA Corp.").Visible = False
.PivotItems("ZYLSTR Corp.").Visible = False
End With
There is nothing new in Excel 2019 VBA to emulate the search box. To achieve the same results in VBA, you use the xlCaptionContains filter described in Table 13-2.
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. You can resize and reposition slicers. You can control the color of the slicer and control the number of columns in a slicer. You can also select or clear items from a slicer by using VBA.
Figure 13-30 shows a pivot table with two slicers. The State slicer has been modified to have five columns. The slicer with the caption “Territory” is actually based on the Region field. You can give slicers friendlier captions, which might be helpful when the underlying field is called IDKTxtReg or some other bizarre name invented by the IT department.
A slicer is composed of SlicerCache and Slicer. To define a slicer cache, you need to specify a pivot table as the source and a field name as SourceField. SlicerCache is defined at the workbook level. This enables you to have the slicer on a different worksheet than the actual pivot table. Here’s the code to do all this:
Dim SCS as SlicerCache
Dim SCR as SlicerCache
Set SCS = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="State")
Set SCR = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="Region")
After you have defined SlicerCache, you can add Slicer, which 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 will be visible in the slicer. Specify the size of the slicer using height and width in points. Specify the location using top and left in points. In the following code, the values for top, left, height, and width are assigned to be equal to the location or size of certain cell ranges:
Dim SLS as Slicer
Set SLS = SCS.Slicers.Add(SlicerDestination:=WSD, Name:="State", _
Caption:="State", _
Top:=WSD.Range("O2").Top, _
Left:=WSD.Range("O2").Left, _
Width:=WSR.Range("O2:U2").Width, _
Height:=WSD.Range("O2:O17").Height)
' Format the color and number of columns
Every slicer starts out as one column. You can change the style and number of columns with this code:
With SLS
.Style = "SlicerStyleLight6"
.NumberOfColumns = 5
End With
Note
I find that when I create slicers in the Excel interface, I spend many mouse clicks making adjustments to them. After adding two or three slicers, Excel positions them in an overlapping tile arrangement. I always tweak the location, size, number of columns, and so on. For many years in my seminars, I bragged that I could create a pivot table in six mouse clicks. That was before slicers were introduced. Slicers are admittedly powerful, but they seem to take 20 mouse clicks before they look right. Having a macro make all of these adjustments at once is a timesaver.
After a 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 SlicerCache, not a member of Slicer:
With SCR
.SlicerItems("Midwest").Selected = True
.SlicerItems("North").Selected = True
.SlicerItems("Northeast").Selected = True
.SlicerItems("South").Selected = False
.SlicerItems("Southeast").Selected = False
.SlicerItems("Southwest").Selected = False
.SlicerItems("West").Selected = False
End With
You might need to deal with slicers that already exist. If a slicer is created for the State field, the slicer cache is named "Slicer_State". The following code is used to format the slicers shown in Figure 13-30:
Sub MoveAndFormatSlicer()
Dim SCS As SlicerCache
Dim SLS As Slicer
Dim SCR As SlicerCache
Dim SLR As Slicer
Dim WSD As Worksheet
Set WSD = ActiveSheet
Set SCS = ActiveWorkbook.SlicerCaches("Slicer_State")
Set SLS = SCS.Slicers("State")
With SLS
.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
Set SCR = ActiveWorkbook.SlicerCaches("Slicer_Region")
Set SLR = SCR.Slicers("Region")
With SLR
.Style = "SlicerStyleLight3"
.NumberOfColumns = 1
.Top = WSD.Range("C1").Top + 5
.Left = WSD.Range("C1").Left - 20
.Width = WSD.Range("C1").Width
.Height = WSD.Range("C1:C14").Height
.Caption = "Territory"
End With
' Choose three regions
With SCR
.SlicerItems("Midwest").Selected = True
.SlicerItems("North").Selected = True
.SlicerItems("Northeast").Selected = True
.SlicerItems("South").Selected = False
.SlicerItems("Southeast").Selected = False
.SlicerItems("Southwest").Selected = False
.SlicerItems("West").Selected = False
End With
End Sub
Using the Data Model in Excel 2019
Excel 2019 incorporates Power Pivot into the core Excel product. This means you can add two tables to the Data Model, create a relationship, add a measure, and then build a pivot table from the Data Model.
To follow along with the example in this section, open the 13-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, you follow these general steps in the macro:
Add the main table to the model.
Add the lookup table to the 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.Add2 Name:="LinkedTable_" & TableName, _
Description:="", _
ConnectionString:="WORKSHEET;" & WBT.FullName, _
CommandText:=WBT.Name & "!" & TableName, _
lCmdType:=7, _
CreateModelConnection:=True, _
ImportRelationships:=False
Several variables in this code use the table name, the workbook path, and/or the workbook name. By storing the table name in a variable at the top of the code, you can build the connection name, connection string, and command text using the variables.
Adapting the preceding code to link to the lookup table then requires only changing the TableName variable:
TableName = "Sector"
WBT.Connections.Add2 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 the following four items in the Create Relationship dialog box (see Figure 13-31):
Table 1 is Sector.
Columns is Customer.
Table 2 is Sales.
Columns is Customer.
The code to create the relationship is more streamlined. There can be only one Data Model per workbook. Set an object variable named MO to refer to the model in this workbook. Use the ModelRelationships.Add method and specify 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. Here’s the code for defining the pivot cache and building the pivot table:
' 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
You need to add two types of fields 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 for 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.
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 With
In 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 With
Adding numeric fields to the Values area
In Excel 2010, Power Pivot calculated fields were called measures. In Excel 2019, the Excel interface calls them calculations. However, the underlying VBA code still calls them measures.
If you have a Data Model pivot table and you check the Revenue field, you see the Revenue field move to the Values area. Behind the scenes, though, Excel is implicitly defining a new measure called Sum Of Revenue. (You can see the implicit measures in the Power Pivot window if you use Excel 2019 Pro Plus.) In VBA, the first step is to define a new measure for Sum Of Revenue. To make it easier to refer to this measure later, assign the new measure to an object variable:
' Before you can add Revenue to the pivot table,
' you have to define the measure.
' This happens using the GetMeasure method.
' Assign the cube field to CFRevenue object
Dim CFRevenue As CubeField
Set CFRevenue = PT.CubeFields.GetMeasure( _
AttributeHierarchy:="[Sales].[Revenue]", _
Function:=xlSum, _
Caption:="Sum of Revenue")
' Add the newly created cube field to the pivot table
PT.AddDataField Field:=CFRevenue, _
Caption:="Total Revenue"
PT.PivotFields("[Measures].[Sum of Revenue]").NumberFormat = "$#,##0,K"
You can use the sample code to create a new measure. The following measure uses the Distinct Count function to count the number of unique customers in each sector:
' Add Distinct Count of Customer as a Cube Field
Dim CFCustCount As CubeField
Set CFCustCount = PT.CubeFields.GetMeasure( _
AttributeHierarchy:="[Sales].[Customer]", _
Function:=xlDistinctCount, _
Caption:="Customer Count")
' Add the newly created cube field to the pivot table
PT.AddDataField Field:=CFCustCount, _
Caption:="Customer Count"
Now that Power Pivot ships with every copy of Excel 2019, 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 13-32 shows the Data Model pivot table created using the code in Listing 13-7.
LISTING 13-7 Code to create the Data Model pivot table in Figure 13-32
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 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 Sub
Next steps
In Chapter 14, “Advanced pivot table tips and techniques,” you’ll learn many techniques for handling common questions and issues related to pivot tables.