Pivot Table and VBA

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.