Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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

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

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

Creating a pivot table

Figure 17-2 shows a pivot table created from the data. This pivot table summarizes the sales performance by sales representative and month. This pivot table is set up with the following fields:

• Region: A report filter field in the pivot table.

• SalesRep: A row field in the pivot table.

• Month: A column field in the pivot table.

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

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

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

Sub RecordedMacro() Sheets.Add ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, _ SourceData:="Sheet1!R1C1:R13C4") _ .CreatePivotTable _ TableDestination:="Sheet2!R3C1", _ TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion12 Sheets("Sheet2").Select With ActiveSheet.PivotTables _ ("PivotTable1").PivotFields("Region") .Orientation = xlPageField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1") _ .PivotFields("Month") .Orientation = xlColumnField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1") _ .PivotFields("SalesRep") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField _ ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Sales"), _ "Sum of Sales", xlSum ActiveSheet.PivotTables("PivotTable1"). _ DisplayFieldCaptions = False End Sub

If you execute this macro, it will probably produce an error. Examine the code, and you'll see that the macro recorder "hard-coded" the worksheet name ( Sheet2 ) for the pivot table. If that sheet already exists (or if the new sheet that's added has a different name), the macro ends with an error.

Examining the recorded code for the pivot table

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

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

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

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

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

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

• AddDataField : A method that adds a field to the Value area.

Cleaning up the recorded pivot table code

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

Sub CreatePivotTable() Dim PTCache As PivotCache Dim PT As PivotTable ' Create the cache Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Range("A1").CurrentRegion) ' Add a new sheet for the pivot table Worksheets.Add ' Create the pivot table Set PT = ActiveSheet.PivotTables.Add( _ PivotCache:=PTCache, _ TableDestination:=Range("A3")) ' Add the fields With PT .PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlDataField 'no field captions .DisplayFieldCaptions = False End With End Sub

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

Excel 2007 pivot tables are easier to use than the old pivot tables. But that's not all that's changed. Here's a list of other new pivot table features:

• Increased limits: More rows and columns , more fields (16,000, up from 255), more unique pivot table items (1 million, up from 32,000), and no more truncated labels.

• Label filtering: For example, you can display only product names that contain the word widget.

• Value filtering: For example, you can display only products that have sales in excess of $5,000.

• Conditional formatting support: You can incorporate the new conditional formatting features in your pivot table (for example, display data bars).

• Pivot chart format persistence: Pivot charts maintain their formatting after the pivot table is refreshed.

• Styles: The new pivot table style gallery makes it easy to change the look of your pivot table with a single mouse click.

Also, it's important to understand that Excel 2007 pivot tables are not backward compatible. If you plan to share a workbook that uses an Excel 2007 pivot table with someone who uses an earlier version, you'll need to use "compatibility mode" and save your workbook in the Excel 97 “2003 XLS file format.

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

Adding the worksheet before the pivot table is created eliminates the need to hard-code the sheet reference. Yet another difference is that the hand-written macro does not specify a pivot table name. Because the PT object variable is created, it's never necessary to refer to the pivot table by name.

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