The Complete Code

For reference, let us put together the code required to create the pivot table in Figure 20-6; it is shown in Example 20-3.

Example 20-3. The CreatePivot Procedure

Sub CreatePivot()

' Create pivot table at active cell

' Assumes that the source table is in sheet called Source

ActiveSheet.PivotTableWizard SourceType:=xlDatabase,

SourceData:="'Company Both'!R1C1:R145C7", TableName:="Sales&Trans"

' Assign field orientations and data fields With ActiveSheet.PivotTables("Sales&Trans")

.PivotFields("Year").Orientation = xlPageField .PivotFields("Year").Position = 1

.PivotFields("Store City").Orientation =

xlRowField .PivotFields("Store City").Position = 1

.PivotFields("Store Type").Orientation =

xlRowField .PivotFields("Store Type").Position = 2

.PivotFields("Period").Orientation = xlColumnField

With .PivotFields("Transactions") .Orientation = xlDataField .Position = 1 End With

With .PivotFields("Sales")

.Orientation = xlDataField .Position = 2 End With

.PivotFields("Data").Orientation = xlRowField

.PivotFields("Data").Position = 3 End With End Sub

Another approach to assigning orientation for the pivot fields is to use the AddFields method of the PivotTable object. We can use this method for all but data fields. The syntax is:

PivotTableObject .AddFields(RowFields,

ColumnFields, PageFields, AddToTable)

The optional RowFields parameter can specify either a single pivot-field name or an array of pivot-field names to be added as rows, and similarly for the ColumnFields and PageFields parameters.

It is important to note that any invocation of the AddFields method will replace all existing fields of the given type (row, column, or page) with the fields designated by the parameters of the method. To increment rather than replace existing fields, we must set the AddToTable parameter to True.

The alternative to CreatePivot shown in Example 20-4 uses the AddFields method for row, column, and page fields. Note that this is shorter than the previous procedure. (It is also the approach taken by Excel itself when we record a macro that creates this pivot table.)

Example 20-4. Creating a Pivot Table Using the AddFields Method

Sub CreatePivot2()

' Create pivot table at active cell

' Assumes that the source table is in sheet called Source

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Source'!R1C1:R14 5C7", TableName:="Sales&Trans2"

ActiveSheet.PivotTables("Sales&Trans2").AddFields _ RowFields:=Array("Store City", "Store Type"), ColumnFields:="Period", PageFields:="Year"

With ActiveSheet.PivotTables("Sales&Trans2") With .PivotFields("Transactions") .Orientation = xlDataField .Position = 1 End With

With .PivotFields("Sales")

.Orientation = xlDataField .Position = 2 End With End With End Sub

0 0

Post a comment