Create an Excel Pivot Table report using VBA

Create an Excel Pivot Table report using VBA


Related Links:

1. Create an Excel Pivot Table report using VBA

2. Referencing an Excel Pivot Table Range using VBA

3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA

4. Excel Pivot Table Address, Location, Move & Copy using VBA

5. Excel Pivot Table Layout and Design, using VBA

6. Excel Pivot Table Properties & Settings, using VBA

7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA

8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA

9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA

10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA

11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA

12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA

13. Create & Customize Excel Pivot Table Charts, using VBA

Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):

Create and Customize a Pivot Table report


Use Visual Basic for Applications (VBA) to Create and Customize PivotTable & PivotChart reports: A PivotTable report can be created, customized and manipulated entirely using VBA. Properties and Methods of the PivotTable object are used to create, customize and manipulate a PivotTable report.

It may be noted that there are differences between Pivot Tables created in Excel 2000, Excel 2002-03, Excel 2007 and Excel 2010. Hence it is important to determine in which version of Excel is vba being executed in, to ensure compatibility and similar default settings between the PivotTable with the corresponding Excel version.

You can create a PivotTable with a specific version in Excel 2007 by using the PivotCaches.Create Method.

In Excel 2007, you can have fully interactive PivotTables versions 0 to 3. In Excel versions prior to Excel 2007, you can have fully interactive PivotTable version numbers 0 to 2 (ie. Excel 2000 & Excel 2002-03 formats), but only a read-only PivotTable version number 3 (ie. Excel 2007 format) and you cannot create a PivotChart from this read-only PivotTable.

Working in Excel 2007 in compatibility mode (ie. when xls file is opened) will save an Excel 2000 or Excel 2002-03 format PivotTable (ie. PivotTable versions 0 to 2). When you save an Excel 2007 file in compatibility mode, all PivotTables of previous versions (0 to 2) will be marked for upgrade to version 3 (xlPivotTableVersion12) and the respective PivotTable will be upgraded when refreshed next. Once the PivotTable is upgraded and converted to version 3, it cannot be converted back to an earlier version.

All features of Excel 2007 can be used when you use a PivotTable version number 3 (ie. Excel 2007 format) in Excel 2007. Using PivotTable version numbers 0 to 2 (ie. Excel 2000 & Excel 2002-03 formats) in Excel 2007 will enable use of most but not all features (esp. some filtering options) of Excel 2007.

In the order of: PivotTable Version, PivotTable Version Number, Excel Version, Excel Version Number

xlPivotTableVersion2000, 0, Excel 2000, 9.0

xlPivotTableVersion10, 1, Excel 2002, 10.0

xlPivotTableVersion11, 2, Excel 2003, 11.0

xlPivotTableVersion12, 3, Excel 2007, 12.0

xlPivotTableVersion14, 4, Excel 2010, 14.0

Note: There is no difference in behavior between xlPivotTableVersion10 and xlPivotTableVersion11.

Application.Version Property – returns the Excel version number in which VBA is being executed viz. 10.0, 11.0, 12.0, etc.

PivotTable.Version Property – returns the PivotTable version number, 1, 2, 3, etc.

—————————————————————————————————————

Example 1:

Use the PivotCaches.Create Method, to add a new PivotTable Cache (version excel 2007). Create a PivotTable report based on a Pivot Cache using the PivotCache.CreatePivotTable method. Add row, column, page (report filter) & data fields.

Image 1

Sub createPivotTable1()
‘refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
‘refer Image 1 for PivotTable report created after running below code

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

‘determine the worksheet which contains the source data
Set wsData = Worksheets(“CarSales”)

‘determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets(“Sheet1”)

‘delete all existing Pivot Tables in the worksheet
‘in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables

If MsgBox(“Delete existing PivotTable!”, vbYesNo) = vbYes Then

PvtTbl.TableRange2.Clear

End If

Next PvtTbl

‘A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.

‘set source data range:
Set rngData = wsData.Range(“A1:G49”)

‘for creating a Pivot Cache (version excel 2007), use the PivotCaches.Create Method. When version is not specified, default version of the PivotTable will be xlPivotTableVersion12.

‘The PivotCache.CreatePivotTable method creates a PivotTable report based on a Pivot Cache. TableDestination is mandatory to specify in the method.

‘create Pivot Cache and PivotTable version excel 2007:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).createPivotTable TableDestination:=wsPvtTbl.Range(“A1”), TableName:=“PivotTable1”, DefaultVersion:=xlPivotTableVersion12

 

Set PvtTbl = wsPvtTbl.PivotTables(“PivotTable1”)

‘Default value of ManualUpdate property is False wherein a PivotTable report is recalculated automatically on each change. Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
PvtTbl.ManualUpdate = True

‘add row, column and page (report filter) fields:
Set pvtFld = PvtTbl.PivotFields(“Year”)
pvtFld.Orientation = xlPageField

Set pvtFld = PvtTbl.PivotFields(“Region”)
pvtFld.Orientation = xlRowField

Set pvtFld = PvtTbl.PivotFields(“Car Models”)
pvtFld.Orientation = xlRowField
pvtFld.Position = 1

Set pvtFld = PvtTbl.PivotFields(“Country”)
pvtFld.Orientation = xlColumnField

‘set data field – specifically change orientation to a data field and set its function property:
With PvtTbl.PivotFields(“Sales”)

.Orientation = xlDataField

.Function = xlSum

.NumberFormat = “#,##0”

.Position = 1

End With

‘turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False

‘Application.Version Property – returns the Excel version number in which VBA is being executed viz. 9.0, 10.0, 11.0, 12.0, etc.
MsgBox Application.Version

‘PivotTable.Version Property – returns the PivotTable version number, 0, 1, 2, 3, etc.
MsgBox PvtTbl.Version

End Sub

—————————————————————————————————————

Example 2:

Use the PivotCaches.Create Method, to add a new PivotTable Cache (version excel 2003). Create a PivotTable report, in a new worksheet, based on a Pivot Cache using the PivotCache.CreatePivotTable method. Add row, column and page (report filter) fields using the AddFields method. Add a data field using the AddDataField method.

Image 2

Sub createPivotTable2()
‘refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
‘refer Image 2 for PivotTable report created after running below code

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim pvtFld As PivotField

‘determine the worksheet which contains the source data
Set wsData = Worksheets(“CarSales”)

‘A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.

‘determine source data range (dynamic):
‘last row in column no. 1:
lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
‘last column in row no. 1:
lastColumn = wsData.Cells(1, Columns.Count).End(xlToLeft).Column

Set rngData = wsData.Cells(1, 1).Resize(lastRow, lastColumn)

‘for creating a Pivot Cache (version excel 2003), use the PivotCaches.Create Method. When version is not specified, default version of the PivotTable will be xlPivotTableVersion12:
Set PvtTblCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion11)

‘create a PivotTable report based on a Pivot Cache, using the PivotCache.CreatePivotTable method. TableDestination is mandatory to specify in this method.

‘create PivotTable in a new worksheet:
ActiveWorkbook.Worksheets(“CarSales”).Activate
Set PvtTbl = PvtTblCache.createPivotTable(TableDestination:=“”, TableName:=“PivotTable1”, DefaultVersion:=xlPivotTableVersion11)

‘rename the new worksheet in which the PivotTable report is created:
ActiveSheet.Name = “PT_CarSales1”

‘change style of the new PivotTable:
PvtTbl.TableStyle2 = “PivotStyleMedium3”

‘to view the PivotTable in Classic Pivot Table Layout, set InGridDropZones property to True, else set to False:
PvtTbl.InGridDropZones = False

‘Default value of ManualUpdate property is False wherein a PivotTable report is recalculated automatically on each change. Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
PvtTbl.ManualUpdate = True

‘Add row, column and page fields in a Pivot Table using the AddFields method:
‘add row, column and page (report filter) fields using the .AddFields method:-
PvtTbl.AddFields RowFields:=Array(“Car Models”, “Region”), ColumnFields:=“Country”, PageFields:=“Year”

‘using the AddDataField method to add a data field in a PivotTable report
‘This method includes 3 arguments of Field, Caption & Summary Function. While it is mandatory to specify the Field object, Caption & Summary Function arguments are optional, default being SUM.
PvtTbl.AddDataField PvtTbl.PivotFields(“Sales”)

Dim PvtDataFld As PivotField
Set PvtDataFld = PvtTbl.PivotFields(“Sum of Sales”)

With PvtDataFld

.NumberFormat = “#,##0”

.Position = 1

End With

‘turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False

‘Application.Version Property – returns the Excel version number in which VBA is being executed viz. 9.0, 10.0, 11.0, 12.0, etc.
MsgBox Application.Version

‘PivotTable.Version Property – returns the PivotTable version number, 0, 1, 2, 3, etc.
MsgBox PvtTbl.Version

End Sub

—————————————————————————————————————–

Example 3:

Add a new PivotTable Cache, using the PivotCaches.Add Method. Create a PivotTable report (version excel 2003) based on a Pivot Cache using the PivotTables.Add Method. Add row, column, page (report filter) & data fields.

Image 3

Sub createPivotTable3()
‘refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
‘refer Image 3 for PivotTable report created after running below code

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

‘add new worksheet
Worksheets.Add
ActiveSheet.Name = “PT_CarSales2”

‘determine the worksheet which contains the source data
Set wsData = Worksheets(“CarSales”)

‘determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets(“PT_CarSales2”)

‘A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.
‘set source data range:
Set rngData = wsData.Range(“A1:G49”)

‘for creating a Pivot Cache, use the PivotCaches.Add Method:
Set PvtTblCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rngData)

‘Create a PivotTable report (version excel 2003) based on a Pivot cache using the PivotTables.Add Method. This is alternate to the PivotCache.CreatePivotTable method mentioned earlier.
‘In the Add method, it is necessary to specify the arguments of PivotCache and TableDestination.
wsPvtTbl.PivotTables.Add PivotCache:=PvtTblCache, TableDestination:=wsPvtTbl.Cells(3, 1), TableName:=“PivotTable1”, DefaultVersion:=xlPivotTableVersion11
Set PvtTbl = wsPvtTbl.PivotTables(“PivotTable1”)

‘Default value of ManualUpdate property is False wherein a PivotTable report is recalculated automatically on each change. Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
PvtTbl.ManualUpdate = True

‘add row, column and page (report filter) fields:
Set pvtFld = PvtTbl.PivotFields(“Year”)
pvtFld.Orientation = xlPageField

Set pvtFld = PvtTbl.PivotFields(“Region”)
pvtFld.Orientation = xlRowField

Set pvtFld = PvtTbl.PivotFields(“Car Models”)
pvtFld.Orientation = xlRowField
pvtFld.Position = 1

Set pvtFld = PvtTbl.PivotFields(“Country”)
pvtFld.Orientation = xlColumnField

‘set data field – specifically change orientation to a data field and set its function property:

With PvtTbl.PivotFields(“Sales”)

.Orientation = xlDataField

.Function = xlSum

.NumberFormat = “#,##0”

.Position = 1

End With

With PvtTbl.PivotFields(“Budgeted Sales”)

.Orientation = xlDataField

.Function = xlSum

.NumberFormat = “#,##0”

.Position = 2

End With

‘turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False

‘Application.Version Property – returns the Excel version number in which VBA is being executed viz. 9.0, 10.0, 11.0, 12.0, etc.
MsgBox Application.Version

‘PivotTable.Version Property – returns the PivotTable version number, 0, 1, 2, 3, etc.
MsgBox PvtTbl.Version

End Sub

————————————————————————————————————

The PivotTableWizard method: PivotTable creation in vba was significantly altered in Excel 2000 when the PivotCache object was introduced, which enables creating a PivotCache and then creating PivotTable reports from the PivotCache. Excel 97 used the PivotTableWizard method to create a PivotTable report in a single step. To create a PivotTable report with vba in Excel 97, the PivotTableWizard method is the only option.

All arguments are optional to specify in the PivotTableWizard method. Omitting SourceType will default to xlDatabase (ie. Excel List or Database); SourceData, though optional, should invariably be specified else Excel considers the source to be a named range “Database” and if this named range does not exist the method will fail if certain conditions are not met. If SourceData is specified then SourceType should also be specified. Omitting the TableDestination argument will place the report at the active cell, and if the active cell lies within the range of SourceData, the report gets placed either in a new worksheet or in the separate worksheet specified in the method by the WorksheetObjectVariable.

Example 4:

Create a PivotTable report using the PivotTableWizard Method. Add row, column and page (report filter) fields using the AddFields method. Add a data field using the AddDataField method.

Image 4

Sub createPivotTable4()
‘refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
‘refer Image 4 for PivotTable report created after running below code

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

‘determine the worksheet which contains the source data
Set wsData = Worksheets(“CarSales”)

‘determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets(“Sheet1”)

‘delete all existing Pivot Tables in worksheet
‘in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property
For Each PvtTbl In wsPvtTbl.PivotTables

If MsgBox(“Delete existing PivotTable!”, vbYesNo) = vbYes Then

PvtTbl.TableRange2.Clear

End If

Next PvtTbl

‘determine source data range (dynamic)
lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
‘last row in column no. 1:
lastColumn = wsData.Cells(1, Columns.Count).End(xlToLeft).Column
‘last column in row no. 1:
Set rngData = wsData.Cells(1, 1).Resize(lastRow, lastColumn)

‘Create a Pivot Table using the PivotTableWizard Method:
‘Create a new Pivot Table based on the Car Sales data table in the CarSales worksheet:-
Set PvtTbl = wsPvtTbl.PivotTableWizard(SourceType:=xlDatabase, SourceData:=rngData, TableDestination:=wsPvtTbl.Range(“A1”), TableName:=“PivotTable1”)

‘Add row, column and page fields in a Pivot Table using the AddFields method:
‘add row, column and page (report filter) fields using the .AddFields method:-
PvtTbl.AddFields RowFields:=Array(“Car Models”, “Region”), ColumnFields:=“Country”, PageFields:=“Year”

‘using the AddDataField method to add a data field in a PivotTable report
‘This method includes 3 arguments of Field, Caption & Summary Function. While it is mandatory to specify the Field object, Caption & Summary Function arguments are optional, default being SUM.
PvtTbl.AddDataField PvtTbl.PivotFields(“Sales”)

Dim PvtDataFld As PivotField
Set PvtDataFld = PvtTbl.PivotFields(“Sum of Sales”)

With PvtDataFld

.NumberFormat = “#,##0”

.Position = 1

End With

‘Application.Version Property – returns the Excel version number in which VBA is being executed viz. 9.0, 10.0, 11.0, 12.0, etc.
MsgBox Application.Version

‘PivotTable.Version Property – returns the PivotTable version number, 0, 1, 2, 3, etc.
MsgBox PvtTbl.Version

End Sub

Leave a Reply

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

Scroll to top