Version 5 of Excel introduced a powerful new macro language called Visual Basic for Application (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets.

Version 5 of Excel introduced a powerful new macro language called Visual Basic for Application (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets.

If you are using Excel 2000 or newer, VBA may be disabled. Before you can start using VBA, you need to enable macros on the Security dialog box. From the application menu, choose Tools, Macro, Security. Set the macro security level to medium. This will allow VBA macros to run, but you will have to explicitly enable them when you open Excel.

From Excel, type Alt+F11 or from the application menu, select Tools, Macro, Visual Basic Editor to open the Visual Basic Editor, as shown in Figure 1. The three main sections of the VBA Editor are described here. If this is your first time using VBA, some of these items may be disabled. Follow the instructions given 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 and code modules present in the workbook. If the Project Explorer is not visible, enable it with Ctrl+R.

• Properties window The Properties window is important when you begin to program user forms. It has some use when writing normal code, so enable it with F4.

• Code window This is the area where you will 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, Code Module from the application menu.

Visual Basic is a powerful development environment. Although this tutorial cannot offer a complete course on VBA, if you are new to VBA, you will want to take advantage of these important tools in VBA:

• As you begin to type code, Excel may offer a drop-down with valid choices. This feature, known as AutoComplete, allows you to type code faster and eliminate typing mistakes.

• For assistance on any keyword, put the cursor in the keyword and press the F1 key. You might need your installation CDs because the VBA help file is not in the default install.

• Excel checks each line of code as you finish it. Lines in error will appear in red. Comments will appear in green. You can add a comment by typing a single apostrophe. Use lots of comments so you can remember what each section of code is doing.

• Despite the aforementioned error checking, Excel may still encounter an error at runtime. If this happens, click the Debug button. The line that caused the error will be highlighted in yellow. Hover your 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. You can toggle back and forth between Excel and VBA to 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 in the Excel help menu.

Excel offers a macro recorder that is about 90% perfect. Unfortunately, the last 10% is frustrating. Code that you record to work with one dataset will be hard-coded to work only with that dataset. This might work fine if your transactional database occupies cells A1:K41550 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 will use the macro recorder to record code, but then expect to have to clean up the recorded code.

If you took a class in BASIC a long time ago, the recorded code in VBA is going to appear rather foreign to you. Whereas BASIC is a procedural language, VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax. Except, in VBA, it is called Object.Method. Objects can be workbooks, worksheets, cells, or ranges of cells. Methods can be typical Excel actions, such as .Copy, .Paste, .PasteSpecial. Many methods allow adverbsparameters you use to specify how to perform the method. If you see a construct with a colon/equal sign, you know that the macro recorder is describing how the method should work. The final type of code that you might see is where you assign a value to the adjectives of an object. In VBA, adjectives are called properties. If you set ActiveCell.Font.ColorIndex = 3, you are setting the font color of the active cell to red. Note that when you are dealing with properties, there is only an equal sign, not a colon/equal sign.

You need to master a few simple techniques in order to write efficient VBA code. These techniques will help you make the jump to writing effective code.

The macro recorder will hard-code that your data is in a range, such as A1:K41550. Although this will work for today's dataset, it may not work as you get new datasets. Write code that can deal with different size datasets.

The macro recorder will use 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 will refer to a rectangular range as Range("A1:K41550"). 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(41550,11).

This is more flexible because you can replace any of the numbers with a variable.

In the Excel user interface, you can use the End key on the keyboard to jump to the end of a range of data. If you move the cellpointer to the final row on the worksheet and press the End key followed by the up-arrow key, the cell pointer will jump to the last row with data. The equivalent of doing this in VBA is to use the following code:

Range("A65536").End(xlUp).Select

You don't need to select this cellyou just need to find the row number that contains the last row. The following code will locate this row and save the row number to a variable named FinalRow:

FinalRow = Range("A65536").End(xlUp).Row

There is nothing magic about the variable name FinalRow. You could call this variable something such as x or y, or even your dog's name. However, because VBA allows you to use meaningful variable names, you should use something such as FinalRow to describe the final row.

It is also possible to find the final column in a dataset. If you are relatively sure that the dataset will begin in row 1, you can use the End key in combination with the left-arrow key to jump from IV1 to the last column with data. To generalize for the possibility that Excel 2006 includes more columns, you can use the following code:

FinalCol = Cells(1, Application.Columns.Count).End(xlToLeft).Column

You might be tempted to find the final row by starting in cell A1 and using the End key in conjunction with the down-arrow key. Avoid this. Data coming from another system is imperfect. If your program will import 50,000 rows from a legacy computer system every day for the next five years, a day will come when someone manages to key a null value into the dataset. This will cause a blank cell or even a blank row to appear in the middle of your dataset. Using Range("A1").End(xlDown) will stop prematurely at the blank cell instead of including all your data. This blank cell will cause that day's report to miss thousands of rows of data, a potential disaster that will call into question the credibility of your report. Take the extra step of starting at the last row in the worksheet in order to greatly reduce the risk of problems.

In typical programming languages, a variable holds a single value. You might use x = 4 to assign a value of 4 to the variable x.

Think about a single cell in Excel. There are many properties that describe a cell. A cell might contain a value such as 4, but the cell also has a font size, 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") will assign the current value of A1 to the variable x. However, use the Set keyword to create an object variable:

Set x = Range("A1")

You've 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 where you can access the value of many properties associated with the variable. You can reference x.Formula to learn the formula in A1, or x.Font.ColorIndex to learn the color of the cell.

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. Therefore, you need to be extremely careful when writing code in Excel 2003 that might be run in Excel 2000 or Excel 97.

Just a few simple tweaks make 2003 code run in 2000, but a major overhaul is required to make 2003 code run in Excel 97. Because it has been eight years since the release of Excel 97 (and because Microsoft has not supported that product for 3+ years), this tutorial will focus on using only the pivot cache method introduced in Excel 2000. At the end of the tutorial, you will briefly learn the PivotTable Wizard method, which is your only option if you need code to run in Excel 97.

Keep in mind that this tutorial is not meant to imply that you use VBA to build pivot tables to give to your users! Rather, its purpose is to remind you that pivot tables can be used as a means to an end; you can use a pivot table to extract a summary of data and then use that summary elsewhere.

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("PivotTable")

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

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

After the pivot cache is defined, 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 2.

If you are using the Layout dialog box 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 page area of the pivot table:

' Set up the row & column fields
PT.AddFields RowFields:=Array("Line of Business", "Model"), _
    ColumnFields:="Region"

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

Excel is smart. When you build a report with revenue, it assumes you want to sum the revenue. But, there is a problem. Say that one of the revenue cells is accidentally blank. When you build the pivot table, even though 99.9% of fields are numeric, Excel assumes you have alphanumeric data and offers to count this field. This is annoying. It seems to be an anomaly that on one hand, you are expected to make sure that 100% of your cells have numeric data, but on the other hand, the results of the pivot table are often filled with non-numeric blank cells.

When you build the pivot table in the Excel interface, you should take care in the Layout dialog box to notice that the field reads Count of Revenue instead of Sum of Revenue. At that point, the right thing is to go back and fix the data, but what people usually do is double-click the Count of Revenue button and change it to Sum of Revenue.

In VBA, you should always explicitly define that you are creating a sum of revenue by explicitly setting the Function property to xlSum:

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

At this point, you've 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. You can immediately thereafter set this back to TRue:

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

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

Here is the complete code used to generate the pivot table:

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

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

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(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)

    ' 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("Line of Business", "Model"), _
        ColumnFields:="Region"

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

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

End Sub

Although pivot tables are incredible, they have annoying limitations. You cannot move or change just a part of a pivot table. For example, try to run a macro that would delete column R, which contains the Grand Total column of the pivot table. The macro comes to a screeching halt with an error 1004, as shown in Figure 4.

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

Because of the limitations of pivot tables, you should generally copy the results of a pivot table to a new location on the worksheet and then delete the original pivot table. The code in CreateSummaryReportUsingPivot() creates a small pivot table. Note that you can set the ColumnGrand and RowGrand properties of the table to False to prevent the totals from being added to the table.

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

The code copies PT.TableRange2 and does a PasteSpecial to a cell three rows below the current pivot table. At that point in the code, your worksheet appears as shown in Figure 5. The table in M2 is a live pivot table, and the table in M16 is just the copied results.

You can then totally 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:

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

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

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(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)

    ' 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:="Model", ColumnFields:="Region"

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

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

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

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

    ' At this point, the worksheet looks like 5

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

The preceding code will create the pivot table. It then copies the results as values and pastes them as values in M16:P25. Figure 5 shows an intermediate result just before the original pivot table is cleared.

So far, you've walked through building the simplest of pivot table reports. Pivot tables offer far more flexibility. Read on for more complex reporting examples.

A typical report might provide a list of models with revenue by year. This report could be given to product line managers to show them which models are selling well. In this example, you want to show the models in descending order by revenue with years going across the columns. A sample report is shown in Figure 6.

The key to producing this data quickly is to use a pivot table. Although pivot tables are incredible for summarizing data, they are quirky and their presentation is downright ugly. The final result is rarely formatted in a manner that is acceptable to line managers. There is not a good way to insert page breaks between each product in the pivot table.

To create this report, start with a pivot table that has Line of Business and Market as row fields, In Balance Date grouped by year as a column field, and Sum of Revenue as the data field. Figure 7 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 7, the value "Copier Sale" appears in the product column only once and is followed by 20 blank cells. This is the worst feature of pivot tables, and there is absolutely no way to correct it. Although humans can understand that this entire section is for product copier sales, it is radically confusing if your Copier section spills to a second or third page. Page 2 starts without any indication that the report is for copier sales. If you intend to repurpose the data, you need the Copier Sales value to be on every row.

• The report contains blank cells instead of zeroes. In Figure 7, customer New England had no copier sales in 2006. Excel produces a pivot table where cell O13 is blank instead of zero. This is simply bad form. Excel experts rely on being able to "ride the range," using the End and arrow keys. Blank cells ruin this ability.

• The title is boring. Most people would agree that "Sum of Revenue" is an annoying title.

• Some captions are extraneous. The words "In Balance Date" floating in cell O2 of Figure 7 really 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 a myriad of borders that really make the report look awful.

• The default number format is General. It would be better to set this up as data with commas to serve as thousands of separators, or perhaps even data in thousands or millions.

• Pivot tables offer no intelligent page break logic. If you want to be able to produce one report for each line of business manager, there is no fast method for indicating that each product should be on a new page.

• Because of the page break problem, you may find it is easier to do away with the pivot table's subtotal rows and have the Subtotal method add subtotal rows with page breaks. You need a way to turn off the pivot table subtotal rows offered for Line of Business in Figure 7. These show up automatically whenever you have two or more row fields. If you would happen to have 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, they are still the way to go. Each complaint can be overcome, 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 dataset.

People started complaining about the blank cells immediately when pivot tables were first introduced. Anyone using Excel 97 or later can easily replace blank cells with zeroes. In the user interface, the setting can be found in the PivotTable Options dialog box. Choose the For Empty Cells, Show option and type 0 in the box.

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

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

PT.PivotFields("Line of Business").AutoSort Order:=xlDescending, _
    Field:="Sum of Revenue"

To change the number format in the user interface, double-click the Sum of Revenue title, click the Number button, and set an appropriate number format.

When you have large numbers, it helps to have the thousands separator displayed. To set this up in VBA code, use the following:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0"

Some companies often have customers who typically buy thousands or millions of dollars of goods. You can display numbers in thousands by using a single comma after the number format. Of course, you will want to include a "K" abbreviation to indicate that the numbers are in thousands:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"

Of course, 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're in luck because Microsoft makes it easy to use the K abbreviation. However, if you work at a 100+ year-old soap company where you use "M" for thousands and "MM" for millions, you have a few more hurdles to jump. You are required to 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 a double quote. To put a double quote inside a quoted string in VBA, you must put two sequential quotes. 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"""

In case it is hard to read, the format is quote, pound, comma, pound, pound, zero, period, zero, comma, comma, quote, quote, M, quote, quote, quote. The three quotes at the end are correct. Use two quotes to simulate typing one quote in the custom number format box and a final quote to close the string in VBA.

As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. However, you may want to suppress subtotals for any number of reasons. Although this may be a relatively simple task to accomplish manually, the VBA code to suppress subtotals is surprisingly complex.

You must set the Subtotals property equal to an array of 12 False values. Read the VBA help for all the gory details, but it goes something like this: 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. It is interesting that you have to turn off all 12 possible subtotals, even though Excel displays only one subtotal. This line of code suppresses the Product subtotal:

PT.PivotFields("Line of Business").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 will automatically turn off the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:

PT.PivotFields("Line of Business").Subtotals(1) = True
PT.PivotFields("Line of Business").Subtotals(1) = False

Because you are going to be using VBA code to add automatic subtotals, you can get rid of the Grand Total row. If you turn off Grand Total for Rows, you delete the column called Grand Total. Thus, to get rid of the Grand Total row, you must uncheck Grand Total for Columns. This is handled in the code with the following line:

PT.ColumnGrand = False

You've reached the end of the adjustments that you can make to the pivot table. To achieve the final report, you have to make the remaining adjustments after converting the pivot table to regular data.

Figure 8 shows the pivot table with all the adjustments described in the previous sections made and with PT.TableRange2 selected.

Say that you want to build the report in a new workbook so that it can be easily mailed to the product managers. This is fairly easy to do. To make the code more portable, assign object variables to the original workbook, the new workbook, and the first worksheet in the new workbook. At the top of the procedure, add these statements:

Dim WSR As Worksheet
Dim WBO As Workbook
Dim WBN As Workbook
Set WBO = ActiveWorkbook
Set WSD = Worksheets("Pivot Table")

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 Market and Year"
    .Font.Size = 14
End With

Imagine that you have submitted the pivot table in Figure 8, and your manager hates the borders, hates the title, and hates the words "Line of Business" in cell O2. You can solve all three of 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 8. 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. Copy the TableRange2 property, offset by one row. Purists will note that this code does copy one extra blank row from below the pivot table, but this really does not matter, because the row is blank. After doing the copy, you can erase the original pivot table and destroy the pivot cache:

' 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.TableRange2.Clear
Set PTCache = Nothing

Note that you used 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 this keeps the data in a pivot table, and you won't be able to insert new rows in the middle of the data.

The report is almost complete. You are nearly a Data, Subtotals command away from having everything you need. Before you can use the Subtotals command, however, you need to fill in all the blank cells in the outline view of column A.

Fixing the Outline view requires just a few obscure steps. Here are the steps in the user interface:

Fixing the Outline view in VBA requires fewer steps. The equivalent VBA logic is shown here:

The code to do this follows:

Dim FinalReportRow as Long
    ' Fill in the Outline view in column A
    ' Look for last row in column B since many rows
    ' in column A are blank
FinalReportRow = WSR.Range("B65536").End(xlUp).Row
With Range("A3").Resize(FinalReportRow - 2, 1)
    With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
    End With
    .Value = .Value
End With

The last steps for the report involve some basic formatting tasks and then adding the sub totals. You can bold and right-justify the headings in row 3. Set rows 13 up so that the top three rows print on each page:

' Do some basic formatting
' Autofit columns, bold the headings, right-align
Selection.Columns.AutoFit
Range("A3").EntireRow.Font.Bold = True
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"

Automatic subtotals are a powerful feature found on the Data menu. Figure 9 shows the Subtotal dialog box. Note the option Page Break Between Groups.

If you were sure that you would always have two years and a total, the code to add subtotals for each Line of Business group would be the following:

' Add Subtotals by Product.
' Be sure to add a page break at each change in product
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5), _
    PageBreaks:=True

However, this code fails if you have more or less than one year. The solution is to use this 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, 255).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 = Range("A65536").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)

Listing 1 produces the product line manager reports in a few seconds.

Listing 1. Code That Produces the Product Line Report in Figure 10

Sub ProductLineReport()
    ' Line of Business and Market 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 GrandRow As Long
    Dim FinalReportRow as Long
    Dim i as Integer
    Dim TotColumns()

    Set WSD = Worksheets("PivotTable")
    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

    ' 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("Line of Business", _
        "In Balance Date"), ColumnFields:="Market"

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

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

    ' Group by Year
    Cells(3, FinalCol + 3).Group Start:=True, End:=True, _
        Periods:=Array(False, False, False, False, False, False, True)

    ' Move In Balance Date to columns
    PT.PivotFields("In Balance Date").Orientation = xlColumnField
    PT.PivotFields("Market").Orientation = xlRowField

    PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"
    PT.PivotFields("Line of Business").Subtotals(1) = True
    PT.PivotFields("Line of Business").Subtotals(1) = False
    PT.ColumnGrand = False

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

    ' PT.TableRange2.Select

    ' 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 Market and Year"
        .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
    PT.TableRange2.Clear
    Set PTCache = Nothing

    ' Fill in the Outline view in column A
    ' Look for last row in column B since many rows
    ' in column A are blank
    FinalReportRow = WSR.Range("B65536").End(xlUp).Row
    With Range("A3").Resize(FinalReportRow - 2, 1)
        With .SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=R[-1]C"
        End With
        .Value = .Value
    End With

    ' Do some basic formatting
    ' Autofit columns, bold the headings, right-align
    Selection.Columns.AutoFit
    Range("A3").EntireRow.Font.Bold = True
    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
    Selection.Subtotal GroupBy:=1, Function:=xlSum, _
        TotalList:=TotColumns, Replace:=True, _
        PageBreaks:=True, SummaryBelowData:=True

    ' Make sure the columns are wide enough for totals
    GrandRow = Range("A65536").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

Figure 10 shows the report produced by this code.

So far, you have built some powerful summary reports, but you've touched only a portion of the powerful features available in pivot tables. The prior example produced a report but had only one data field.

It is possible to have multiple fields in a pivot report.

The data in this example includes not just revenue, but also units. The CFO will probably appreciate a report by product that shows quantity sold, revenue, and average price.

When you have two or more data fields, you have a choice of placing the data fields in one of four locations. By default, Excel builds the pivot report with the data field as the innermost row field. It is often preferable to have the data field as the outermost row field or as a column field.

When a pivot table is going to have more that one data field, you have a virtual field named Data. Where you place the Data field in the .AddFields method determines which view of the data you get.

The default setup, with the data fields arranged as the innermost row field, as shown in Figure 11, would have this AddFields line:

PT.AddFields RowFields:=Array("Line of Business", "Data")

The view shown in Figure 12 would use this code:

PT.AddFields RowFields:=Array("Data", "Line of Business")

The view that you need for this report would have Data as a column field:

PT.AddFields RowFields:="Model", ColumnFields:="Data"

After adding a column field called Data, you would then go on to define two data fields:

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

With PT.PivotFields("Units Sold")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 2
    .NumberFormat = "#,##0"
End With

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

To set up a calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula. Note that if you create a field called Average Price, the default pivot table produces a field called Sum of Average Price. This is misleading and downright silly. What you have is actually the average of the sums of prices. The solution is to use the Name property when defining the data field to replace Sum of Average Price with something such as Avg Price. Note that this name must be different from the name for the calculated field.

Listing 2 produces the report shown in Figure 13.

Listing 2. Code That Calculates an Average Price Field as a Second Data Field

Sub TwoDataFields()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    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

    ' 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:="Data"

    ' Define Calculated Fields
    PT.CalculatedFields.Add Name:="AveragePrice", Formula:="=Revenue/Units Sold"

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

    With PT.PivotFields("Units Sold")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2
        .NumberFormat = "#,##0"
    End With

    With PT.PivotFields("AveragePrice")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 3
        .NumberFormat = "#,##0.00"
        .Name = "Avg Price"
    End With

    ' Ensure that you get zeroes instead of blanks in the data area
    PT.NullString = "0"

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

End Sub

Say that in your company the vice president of sales is responsible for copier sales and printer sales. The idea behind a calculated item is that you can define a new item along the Line of Business field to calculate the total of copier sales and printer sales. Listing 3 produces the report shown in Figure 14.

Listing 3. Code That Adds a New Item Along the Line of Business Dimension

Sub CalcItemsProblem()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    Dim WSR As Worksheet

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

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

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

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

    ' Set up the row fields
    PT.AddFields RowFields:="Line of Business"

    ' Define calculated item along the product dimension
    PT.PivotFields("Line of Business").CalculatedItems _
        .Add "PrinterCopier", "='Copier Sale'+'Printer Sale'"
    ' Resequence so that the report has printers and copiers first
    PT.PivotFields("Line of Business"). _
        PivotItems("Copier Sale").Position = 1
    PT.PivotFields("Line of Business"). _
        PivotItems("Printer Sale").Position = 2
    PT.PivotFields("Line of Business"). _
        PivotItems("PrinterCopier").Position = 3

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

    ' Ensure that you get zeroes instead of blanks in the data area
    PT.NullString = "0"

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

End Sub

Look closely at the results shown in Figure 14. The calculation for PrinterCopier is correct. PrinterCopier is a total of Printers + Copiers. Some quick math confirms that 86 million + 68 million is about 154 million. However, the grand total should be 154 million + 83 million + 574 million, or about 811 million. Instead, Excel gives you a grand total of $968 million. The total revenue for the company just increased by $150 million. Excel gives the wrong grand total when a field contains both regular and calculated items. The only plausible method for dealing with this is to attempt to hide the products that make up PrinterCopier. The results are shown in Figure 15:

With PT.PivotFields("Line of Business")
    .PivotItems("Copier Sale").Visible = False
    .PivotItems("Printer Sale").Visible = False
End With

With transactional data, you will 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. For anyone who has 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.

Creating a 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. This is the first example in this tutorial where you must allow VBA to calculate an intermediate pivot table result.

You must define a pivot table with In Balance Date in the row field. Turn off ManualCalculation to allow the Date field to be drawn. You can then use the LabelRange property to locate the date label and group from there. Figure 16 shows the result of Listing 4.

Listing 4. Code That Uses the Group Feature to Roll Daily Dates Up to Monthly Dates

Sub ReportByMonth()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    Dim WSR As Worksheet

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

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

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

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

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

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

    ' Ensure that you get zeroes 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

    ' Group ShipDate by Month, Quarter, Year
    PT.PivotFields("In Balance Date").LabelRange.Group Start:=True, _
        End:=True, Periods:= _
        Array(False, False, False, False, True, True, True)

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

End Sub

You probably noticed that Excel allows you to group by day, month, quarter, and year. There is no standard grouping for week. You can, however, define a group that bunches up groups of seven days.

By default Excel starts the week based on the first date found in the data. This means that the default week would run from Tuesday January 1, 2006 through Monday December 31, 2007. You can override this by changing the Start parameter from true to an actual date. Use the WeekDay function to determine how many days to adjust the start date.

There is one limitation to grouping by week. When you group by week, you cannot also group by any other measure. It is not valid to group by week and quarter.

Listing 5 creates the report shown in Figure 17.

Listing 5. The Code Used to Group by Week Must Figure Out the Monday Nearest the Start ofYour Data

Sub ReportByWeek()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    Dim WSR As Worksheet

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

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

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

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

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

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

    ' Ensure that you get zeroes 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

    ' Group Date by Week.
    'Figure out the first Monday before the minimum date
    FirstDate = PT.PivotFields("In Balance Date").LabelRange. _
        Offset(1, 0).Value
    WhichDay = Weekday(FirstDate, 3)
    StartDate = FirstDate - WhichDay
    PT.PivotFields("In Balance Date").LabelRange.Group _
        Start:=StartDate, End:=True, By:=7, _
        Periods:=Array(False, False, False, True, False, False, False)

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

End Sub

You may be a pivot table pro and never have run into some of the really advanced techniques available with pivot tables. The next four sections discuss such techniques.

If you are designing an executive dashboard utility, you might want to spotlight the top five markets.

As with the AutoSort option, you could be a pivot table pro and never have stumbled across the AutoShow feature in Excel. 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 following code, this is achieved by removing the Market field from the pivot table and copying the grand total to the report. Listing 6 produces the report shown in Figure 18.

Listing 6. 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("PivotTable")

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

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

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

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

    ' Set up the row fields
    PT.AddFields RowFields:="Market", ColumnFields:="Line of Business"

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

    ' Ensure that you get zeroes 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 ritle 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(65536, 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
    WSR.Cells(LastRow + 2, 1).Value = "Total Company"

    ' 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, 6)).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 went back to the pivot table, removed the AutoShow option, and grabbed the total of all markets to produce the Total Company row.

Take any pivot table in the Excel user interface. Double-click any number in the table. Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to ad-hoc query a dataset.

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

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

Listing 7 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 19 shows the first sheet created.

Listing 7. Code That Uses the ShowDetail Method to Provide Detail for the Top Three Customers

Sub RetrieveTop3StoreDetail()
    ' Retrieve Details from Top 3 Stores
    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("PivotTable")

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

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

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

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

    ' Set up the row fields
    PT.AddFields RowFields:="Store", 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("Store").AutoSort Order:=xlDescending, _
        Field:="Total Revenue"

    ' Show only the top 3 stores
    PT.PivotFields("Store").AutoShow Type:=xlAutomatic, Range:=xlTop, _
        Count:=3, Field:="Total Revenue"

    ' Ensure that you get zeroes 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

A pivot table can have one or more page fields. A page field goes in a separate set of rows above the pivot report. It can serve to filter the report to a certain region, a certain model, or a certain combination of region and model.

To set up a page field in VBA, add the PageFields parameter to the AddFields method. The following line of code creates a pivot table with Region in the page field:

PT.AddFields RowFields:="Model", ColumnFields:="Data", PageFields:="Region"

The preceding line of code sets up the Region page field set to 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 page field is to build a user form where someone can select a particular region or a particular product. You then use this information to set the CurrentPage property and display the results of the user form.

Another interesting use is to 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 tutorial, 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 statement to get the detail rows:

PT.TableRange2.Offset(3, 0)
PT.TableRange1.Offset(1, 0)

Which you use is your preference, but if you use TableRange2, you won't 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 8 produces a new workbook for each region, as shown in Figure 20.

Listing 8. Code That Creates a New Workbook per Region

Sub Top5ByRegionReport()
    ' Produce a report of top 5 stores 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("PivotTable")

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

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

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

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

    ' Set up the row fields
    PT.AddFields RowFields:="Store", 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("Store").AutoSort Order:=xlDescending, _
        Field:="Total Revenue"

    ' Show only the top 5 stores
    PT.PivotFields("Store").AutoShow Type:=xlAutomatic, Range:=xlTop, _
        Count:=5, Field:="Total Revenue"

    ' Ensure that you get zeroes 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 Stores in the " & PivItem.Name & " Region"
            .Font.Size = 14
        End With

        ' Copy the pivot table data to row 3 of the report sheet
        ' Use offset to eliminate the page & title rows of the pivot table
        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

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

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

    ' Now - loop through and keep only certain items visible
    For Each PivItem In _
        PT.PivotFields("Line of Business").PivotItems
        Select Case PivItem.Name
            Case "Copier Sale", "Printer Sale"
                PivItem.Visible = True
            Case Else
                PivItem.Visible = False
        End Select
    Next PivItem

If your company has been reporting regions in the sequence of South, North, West forever, it is an uphill battle getting managers to accept seeing the report ordered North, South, West just because this is the default alphabetical order offered by pivot tables.

Strangely enough, Microsoft offers a bizarre method for handling a custom sort order in a pivot table. It's called a manual sort order. To change the sort order in the user interface, you simply go to a cell in the pivot table that contains "North," type the word "South," and press Enter. As if by magic, North and South switch places. Of course, all the numbers for North move to the appropriate column.

The VBA code to do a manual sort involves setting the Position property for a specific PivotItem. This is somewhat dangerous because you don't know whether the underlying data will have data for "South" on any given day. Be sure to set Error Checking to resume in case South doesn't exist today:

On Error Resume Next
PT.PivotFields("Region").PivotItems("South").Position = 1
On Error GoTo 0

So far, every example in this tutorial has involved summing data. It is also possible to get an average, minimum, or maximum of data. In VBA, change the Function property of the data field and give the data field a unique name. For example, the following code fragment produces five different summaries of the quantity field, each with a unique name:

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

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 2
        .NumberFormat = "#,##0"
        .Name = "Number Orders"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlAverage
        .Position = 3
        .NumberFormat = "#,##0"
        .Name = "Average Revenue"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlMin
        .Position = 4
        .NumberFormat = "#,##0"
        .Name = "Smallest Order"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlMax
        .Position = 5
        .NumberFormat = "#,##0"
        .Name = "Largest Order"
    End With

The resultant pivot table provides a number of statistics about the average revenue, largest order, smallest order, and so on.

In addition to the available choices, such as Sum, Min, Max, and Average, there is another set of pivot table options called the calculation options. These allow you to show a particular field as a percentage of the total, a percentage of the row, a percentage of the column, or as the percent difference from the previous or next item. All these settings are controlled through the .Calculation property of the page field.

The valid properties for .Calculation are xlPercentOf, xlPercentOfColumn, xlPercentOfRow, xlPercentOfTotal, xlRunningTotal, xlPercentDifferenceFrom, xlDifferenceFrom, xlIndex, and xlNoAdditionalCalculation. Each has its own unique set of rules. Some require that you specify a BaseField, and others require that you specify both a BaseField and a BaseItem. The following sections have some specific examples.

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

' Set up a percentage of total
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Caption = "PctOfTotal"
    .Function = xlSum
    .Position = 2
    .NumberFormat = "#0.0%"
    .Calculation = xlPercentOfTotal
End With

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

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

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

Many companies have a goal to have service revenue exceed a certain multiplier of copier sales. You can use the xlPercentDifferenceFrom setting to express revenues as a percentage of the copier product line:

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

It is not intuitive, but to set up a running total, you must define a BaseField. In this example, you have In Balance Date running down the column. To define a running total column for revenue, you must specify that BaseField is "In Balance Date":

' Set up Running Total
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "YTD Total"
    .Calculation = xlRunningTotal
    .Position = 4
    .NumberFormat = "#,##0,K"
    .BaseField = "In Balance Date"
End With

Figure 21 shows the results of a pivot table with three custom calculation settings, as discussed earlier.

Pivot tables and VBA took a radical turn in Excel 2000. In Excel 2000, Microsoft introduced the PivotCache object. This object allows you to define one pivot cache and then build many pivot reports from the pivot cache.

Officially, Microsoft quit supporting Excel 97 a few years ago. But, in practical terms, there are still many companies using Excel 97. If you need your code to work on a legacy platform, you should be aware of how pivot tables were created in Excel 97.

In Excel 97, you would use the PivotTableWizard method. Take a look at the code for building a simple pivot table showing revenue by region and line of business. Where current code uses two steps (add a PivotCache and then use CreatePivotTable), Excel 97 would use just one step, using the PivotTableWizard method to create the table:

Sub PivotExcel97Compatible()
    ' Pivot Table Code for Excel 97 Users
    Dim WSD As Worksheet
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")

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

    ' Define input area
    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)

    ' Create pivot table using PivotTableWizard
    Set PT = WSD.PivotTableWizard(SourceType:=xlDatabase, _
        SourceData:=PRange.Address, _
        TableDestination:="R2C13", TableName:="PivotTable1")

    PT.ManualUpdate = True
    ' Set up the row fields
    PT.AddFields RowFields:="Region", ColumnFields:="Line of Business"

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

    PT.ManualUpdate = False
    PT.ManualUpdate = True
End Sub

discuss this topic to forum