Cleaning up the recorded pivot table code

As with most recorded macros, the preceding example is not as efficient as it could be. It can be simplified to make it more understandable. Listing 17-1 generates the same pivot table as the procedure previously listed.

Listing 17-1: A More Efficient Way to Generate a Pivot Table in VBA

Sub CreatePivotTable()

Dim PTCache As PivotCache Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlDatabase, _

SourceData:=Range("A1").CurrentRegion.Address)

Set PT = PTCache.CreatePivotTable _ (TableDestination:="", _ TableName:="PivotTable1")

With PT

.PivotFields("Region").Orientation = xlPageField .PivotFields("Month").Orientation = xlColumnField .PivotFields("SalesRep").Orientation = xlRowField .PivotFields("Sales").Orientation = xlDataField End With End Sub

The CreatePivotTable procedure is simplified (and might be easier to understand) because it declares two object variables: PTCache and PT. These take the place of the indexed references to ActiveSheet.PivotCaches and ActiveSheet. PivotTables. A new PivotCache object is created by using the Add method. Then a new PivotTable object is created by using the CreatePivotTable method of the PivotCaches collection. The last section of the code adds the fields to the pivot table and specifies their location within it (page, column, row, or data field).

Notice that the original macro hard-coded the data range used to create the PivotCache object (that is, 'Sheet1!R1C1:R13C4'). 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 when more data is added.

The code also could be more general through the use of indices rather than literal strings for the PivotFields collections.This way,if the user changes the column headings, the code will still work. For example, more general code would use PivotFields(1) rather than PivotFields('Region'). This alternative is best suited for situations in which the the columns will never be rearranged.

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 online 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.

0 0

Post a comment