The Macro Code

Listing 6-1 shows the code the Macro Recorder generated for us.

Listing 6-1. MakePivotTable Macro Code Sub MakePivotTable()

' MakePivotTable Macro

Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "Sheet1!R1Q:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion ^ :=xlPivotTableVersion12 Sheets("Sheet4").Select Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("State") .Orientation = xlRowField .Position = 1 End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product") .Orientation = xlColumnField .Position = 1 End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(w

"PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum With ActiveSheet.PivotTables("PivotTable1").PivotFields("City") .Orientation = xlRowField .Position = 2 End With End Sub

The first thing the code does is add a new worksheet to the workbook. Then it creates the PivotTable using the source data range we provided in the Create PivotTable dialog box. Then it places the PivotTable on the new sheet (in this case Sheet4) and gives it a default name.

Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= w "Sheet1!R1C1:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable w TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion w :=xlPivotTableVersion12

The PivotCaches.Create method takes three arguments, of which only one (SourceType) is required. The SourceData argument is required when SourceType does not equal xlExternal. Table 6-1 lists the PivotCaches.Create method's arguments and describes them.

Table 6-1. PivotCaches.Create Method Arguments

Name

Required (Y/N)

Data Type

Description

SourceType

Y

xlPivotTableSourceType

Choices are xlConsolidation, xlDatabase, orxlExternal

SourceData

N

Variant

The data for the new PivotTable cache

Version

N

Variant

Version of the PivotTable

The PivotCaches.Create method returns a PivotCache object. The Macro Recorder very cleverly calls the CreatePivotTable method based on the return from the Create method in one long (but readable) line of code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= w "Sheet1!R1C1:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable w TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion w :=xlPivotTableVersion12

The CreatePivotTable method defines where the table will be placed, its name, and its default version. Table 6-2 lists the CreatePivotTable method's arguments.

Table 6-2. CreatePivotTable Method Arguments

Name

Required (Y/N)

Data Type

Description

TableDestination

Y

Variant

The cell in the top-left corner of the PivotTable's destination range.

TableName

N

Variant

The name of the PivotTable report.

ReadData

N

Variant

Set to True to create a PivotTable cache that contains all of the records from the data source (can be very large). Set to False to enable setting some fields as server-based page fields before the data is read.

DefaultVersion

N

Variant

The default version of the PivotTable report.

The code then selects the new sheet and the starting range location for the PivotTable.

Sheets("Sheet4").Select Cells(3, l).Select

We added two text fields (State and Products) to the PivotTable Field List pane and one data field containing the item quantities (Qty):

With ActiveSheet.PivotTables("PivotTable1").PivotFields("State") .Orientation = xlRowField .Position = 1 End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product") .Orientation = xlColumnField .Position = 1 End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( ^ "PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum

This is where the code is telling the PivotTable how to display the data assigned to each PivotField object. The Orientation property is set to a value of the xlPivotFieldOrientation enumeration type, as shown in Table 6-3.

Table 6-3. xlPivotFieldOrientation Enumerations

Name

Value

Description

xlRowField

1

Row

xlColumnField

2

Column

xlPageField

3

Page

xlDataField

4

Data

xlHidden

0

Hidden

The Position property notes where in the row or column hierarchy the field belongs, and therefore how the data will be grouped on the PivotTable. After we added the City field to the Row Labels list in the PivotTable Field List pane, the next bit of code was added:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("City") .Orientation = xlRowField .Position = 2

End With

Notice that its Orientation property is set to xlRowField, denoting row data, and its position is 2. So in the table's rows, we have State in position 1 and City in position 2. If you refer back to Figure 6-8, you can see the data hierarchy displayed.

Let's save this workbook as a macro-enabled workbook. Click the Office button and choose Save As > Excel Macro-Enabled Workbook, leaving the name the same (except for the extension), as shown in Figure 6-10.

Figure 6-10. Saving the file as macro-enabled

Unfortunately, if we rerun the MakePivotTable macro again, we'll get an error, as shown in Figure 6-11.

Microsoft Visual Basic

Run-time error '1004':

A PivotTable report cannot overlap another PivotTable report.

Figure 6-11. RunningMakePivotTable a second time generates an error.

Was this article helpful?

0 0

Post a comment