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.

1 hilUi

I !tJB "JjOOH Ji.lJt Ili/Jt i d» u.m xi.Irj: J^L-J

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

!-.■'<■-I- Iknlli bta

CJFDM

r

LS jtft

T.

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.

0 0

Post a comment