To create a PivotTable, first define the PivotTable cache using PivotCaches.Create. Then create the PivotTable using PivotCaches.Add
PivotCaches.Create has the following parameters:
PivotCaches .Create(SourceType, SourceData, Version)
SourceType can be xlConsolidation, xlDatabase, or xlExternal.
Version can be…
| Name | Value | Description |
|---|---|---|
| xlPivotTableVersion2000 | 0 | Excel 2000 |
| xlPivotTableVersion10 | 1 | Excel 2002 |
| xlPivotTableVersion11 | 2 | Excel 2003 |
| xlPivotTableVersion12 | 3 | Excel 2007 |
| xlPivotTableVersion14 | 4 | Excel 2010 |
| xlPivotTableVersion15 | 5 | Excel 2013 |
| xlPivotTableVersionCurrent | -1 | Provided only for backward compatibility |
PivotCaches.Add has the following parameters
PivotCaches.Add(PivotCache, TableDestination, TableName,ReadData, DefaultVersion)
The code below relates to the data shown above.
Dim ws As Worksheet
Set ws = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim PTcache As PivotCache
Dim PT As PivotTable
'Define the cache for the PivotTable
Set PTcache =wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15)
'Create the PivotTable
Set PT =ws.PivotTables.Add(PivotCache:=PTcache, _
TableDestination:=Range("J1"),TableName:="Sales Analysis")
With PT
.PivotFields("Branch").Orientation = xlRowField
.PivotFields("ProductCategory").Orientation = xlColumnField
.PivotFields("Region").Orientation= xlPageField
.PivotFields("Revenue").Orientation = xlDataField
.DataBodyRange.NumberFormat ="£#,##0;-£#,##0"
End With
The code above would create the PivotTable shown below
Alternatively use the AddFields and AddDataFields of thePivotTable object, as shown below. Use array to specify more than one field.
Dim ws As Worksheet
Set ws = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim PTcache As PivotCache
Dim PT As PivotTable
'Define the cache for the PivotTable
Set PTcache =wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15)
'Create the PivotTable
Set PT =ws.PivotTables.Add(PivotCache:=PTcache, _
TableDestination:=Range("J1"),TableName:="Sales Analysis")
PT.AddFieldsRowFields:=Array("Region", "Branch"), _
ColumnFields:="Product",PageFields:="Product Category"
PT.AddDataFieldField:=PT.PivotFields("Revenue"), _
Caption:="No of Transactions", Function:=xlCount
The code above would create the PivotTable shown below.
