Creating a pivot table
Below
Figure shows a pivot table created from the data, along with the PivotTable
Field List task bar. This pivot table summarizes the Salary of Employee and
month. This pivot table is set up with the following fields:
v Department: A report filter field in the pivot table.
v Employee Name: A row field in the pivot
table.
v Month: A column field in the pivot table.
v Salary: A values field in the pivot table that uses the Sum
function.
I
turned on the macro recorder before I created this pivot table and specified a
new worksheet for the pivot table location. The code that was generated
follows:
Sub CreatePivot()
'This Macro is
recorded and done necessary changes
Range(“A1:E28”).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Monthwise Salary
Register'!R1C1:R28C5", Version:=xlPivotTableVersion12). _
CreatePivotTable
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
.Orientation = xlPageField
.Position = 1
End With
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee
Name")
.Orientation = xlRowField
.Position = 1
End With
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Salary"),
"Sum of Salary", xlSum
End Sub
Cleaning up the recorded
pivot table code
As
with most recorded macros, the preceding example isn’t as efficient as it could
be. And, as I noted, it’s very likely to generate an error. You can simplify
the code 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”))
‘ Specify the
fields
With PT
.PivotFields(“Department”).Orientation
= xlPageField
.PivotFields(“Month”).Orientation
= xlColumnField
.PivotFields(“Employee
Name”).Orientation = xlRowField
.PivotFields(“Salary”).Orientation
= xlDataField
‘no field
captions
.DisplayFieldCaptions
= False
' Change the
captions
.PivotFields("Sum
of Salary").Caption = " Salary "
.PivotFields("Sum
of Incentive").Caption = " Incentive "
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 four fields to the pivot
table and specifies their location within it by assigning a value to the
Orientation property. The original macro hard-coded both the data range used to
create the PivotCache object (‘'Monthwise Salary Register'!R1C1:R28C5’) and the
pivot table location (Sheet2 or Sheet3). 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
doesn’t specify a pivot table name. Because the PT object variable is created, your
code doesn’t ever have to refer to the pivot table by name.
Note: Changes the captions
displayed in the pivot table. For example, Sum of Salary is replaced by Salary.
Note that the string Salary is preceded by a space. Excel doesn’t allow you to
change a caption that corresponds to a field name, so adding a space gets
around this restriction.
