Create Pivot Table using Excel VBA

This Excel tutorial explains how to create Pivot Table using Excel VBA, set Print Area for Pivot Table

Create Pivot Table using Excel VBA

Create Pivot Table using Excel VBA is very straight forward and easy to use. If you don’t understand any Object or Property that VBA Pivot Table uses, simply use “Record Macro” function to do recording to see the underlying VBA code behind each action, such as create Pivot Table, change Pivot Table Fields, select Critera, delete Pivot Table, etc.

In this post, I will summarize the code for the basic actions to save your time.

If you don’t know how to use “Record Macro” function, click to read the below article.

Excel Record Macro

Create Empty Pivot Table

The follow example shows how to create a Pivot Table in Sheet5 A1, from data source in Sheet1 A1:B7

Source table:

VBA_pivottable_000

Public Sub createPivot()
    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7")). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1"
End Sub
VBA Code Type Meaning Example
PivotCaches Object Pivot Table stores its source data and workings in cache memory called PivotCaches, in order that Pivot Table can run faster
Source Type Property Type of data source, parameters include:
xlConslidation
xlDatabase
xlExternal
xlPivotTable
xlScenario
xlDatabase
Source Data Property Where the source data locate Worksheets(“Sheet1”).Range(“A1:B7”)
TableDestination Property Where the Pivot Table is to be placed Worksheets(“Sheet5”).Range(“A1”)
TableName Property The name for Pivot Table PivotTable1

Below is the result you will get from the code, just an empty Pivot Table with no fields.

VBA_pivottable_001

Add PivotFields to Pivot Table

Now try to add Employee ID to Row, Audit to Column, and then Count Employee ID.

VBA_pivottable_002

Public Sub createPivot()
    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7")). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1"
        Set PT = Worksheets("Sheet5").PivotTables("PivotTable1")
        With PT.PivotFields("Empl ID")
            .Orientation = xlRowField
            .Position = 1
        End With
        With PT.PivotFields("Department")
            .Orientation = xlColumnField
            .Position = 1
        End With
         With PT.PivotFields("Empl ID")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlCount
        End With  
End Sub   

The above code is very straight forward and easy to understand, each color represents each set of actions.

The first action is to add Row, then add Column, finally add Data and apply aggregate function “count”. (default is “sum” if Function Property is omitted for DataField)

If you want to add multiple column / row, just copy the code from “With” to “End With”, then change the PivotFields name and then change Position number to increase by 1.

VBA Code Type Meaning Example
PivotFields Object Column data in your data source Empl ID, Department
Orientation Property Where you want to place your column data, parameters include:
xlRowField       (Rows)
xlColumnField   (Columns)
xlPageField       (Filter)
xlDataField       (Values)
xlHiddenFields
Position Property The order of Orientation If both Empl ID and Department are added as xlColumnField, data will group by position 1 and then by position 2
Caption Property Rename PivotField. You can see the new name in the Pivot Table if using 2003 layout
Function Property Aggregate function or statistics function of the data field, parameter include
xlAverage
xlCount
xlCountNums (count numeric values)
xlMax
xlMin
xlProduct (multiply all data)
xlStDev (standard deviation of sample)
xlStDevP (standard deviation of population)
xlSum
xlVar (variance of sample)
xlVarP (variance of population)

Set Print Area for Pivot Table using VBA

Sub printPivot()
 ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
 ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub

What’s Next?

Now you have created a Pivot Table below, which is known as a “Compact Layout”. If you remember in Excel 2003, the layout is quite different (at least 2003 does not have “Row Labels” and “Column Labels”).

VBA_pivottable_002

Read the below article to change a different layout.

Change Pivot Table Layout using VBA

 

Outbound References

http://msdn.microsoft.com/en-us/library/office/aa215695%28v=office.11%29.aspx

https://www.youtube.com/watch?v=M04rnVoD5mc

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *