VBA Code
Properties and methods of the PivotTable object are used for manipulating the visual display of data within the pivottable on the worksheet
PivotTable fields can exist in one of four possible areas: row, column, page and data.
Pivot Tables can be used for:
1) import external data (eg from Access)
2) aggregating data
link - learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/hh243933(v=office.14)
link - learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/ff536099(v=office.12)
Activesheet.PivotTables("-").PivotFields("-").PivotItems("-").Visible = True
All the data in a Pivot table can be manipulated entirely using VBA.
Important Objects
PivotCaches - A collection of pivot cache objects in a workbook object.
PivotTables - A collection of pivot table objects in a worksheet object.
PivotTableFields - A collection of fields in a pivot table.
CreatePivotTable - Creates a pivot table using the data in a pivot cache. A PivotCache object method that creates a pivot table using the data in PivotCache.
PivotTableWizard - Creates a pivot table or modifies an existing pivot table. A worksheet object method that creates a pivot table.
PivotSelect Method
objPivotTable.PivotSelect(Name, Mode)
Name - This is a string expression specifying which part of the pivot table
Mode - xlPTSelectionMode.
ActiveSheet.PivotTables(1).ListFormulas
ActiveSheet.PivotTables(1).RefreshTable
ActiveSheet.PivotTables("-").TableRange1AutoFormat Format:=xlClassic3
Moving
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,1)
Calculated Fields
You can use the CalculatedFields collection of the PivotTable object to add a new calculated field.
Once created it is treated the same as any other field.
Set objPivotTable = ActiveSheet.PivotTables("PivotTable1")
objPivotTable.CalculatedFields.Add "Name", "=formula"
Calculated Items
Grouping and Ungrouping
VBA - PivotCharts
By default a new PivotChart always appears on a chart sheet but you can change its location.
In the case of PivotCharts there is a PivotLayout object which is a member of the Chart object.
VBA - Creating
A pivottable is a buffer where the data is temporarily stored for quick access.
It acts as the intermediate between the data source and the actual pivot table
You can create a pivot cache using the Add method of the PivotCaches collection
You can also use the Pivot caches to generate multiple pivot tables using the same data source
Dim objPivotCache As PivotCache
Dim objPivotTable As PivotTable
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlPivotTableSourceType.xlDatabase, _
SourceData:="Named_Range")
SourceType - The source of the PivotTable cache data.
SourceData - The data for the new PivotTable cache. This argument is required if SourceType isn't xlExternal. Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.
SourceDate - a two or more element array containing information about the data source and SQL query
If the SQL statement is longer than 255 characters then it must be broken up and passed as multiple entries in the array.
sConnection = "ODBC;" & _
"DBQ=" & ThisWorkbook.Path & "\sample.mdb;" & _
"Driver = {Microsoft Access Driver (*.mdb)};"
sQuery = "SELECT * FROM Table_Name"
Dim QueryArray As Variant
Set QueryArray = Array(sConnection,sQuery)
SourceData := QueryArray
Uses a query stored in the Access Database
sQuery = "SELECT * FROM Query1"
Set objPivotTable = ActiveSheet.PivotTables.Add(PivotCache:=cache, _
TableDestination:=""
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10)
PivotCache - The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.
TableDestination - The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed). You must specify a destination range on the worksheet that contains the PivotTables object specified by expression .
TableName - The name of the new PivotTable report.
ReadData - True to create a PivotTable cache that contains all records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.
DefaultVersion - The version of Microsoft Excel the PivotTable was originally created in.
You can also create a pivottable using the CreatePivotTable method of the PivotCache object
ActuveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="Named_Range").CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
By default the top left cell of the range is selected after using the CreatePivotTable method
Range("A1").Select
ACtiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Sheet1!R1C1:R13C4").CreatePivotTable TableDestination:="", TableName:="PivotTable1"
With ActiveSheet.PivotTables("PivotTable1")
.PivotFields(" ").Orientation = xlPageField
.PivotFields(" ").Position:=1
.PivotFields(" ").Orientation = xlColumnField
.PivotFields(" ").Position:=1
.PivotFields(" ").Orientation = xlRowField
.PivotFields(" ").Position:=1
.PivotFields(" ").Orientation = xlDataField
.PivotFields(" ").Position:=1
End WIth
This line will display the grand columns total
Activesheet.PivotTables("PivotTable1").ColumnGrand = True
This line will display the grand row totals
Activesheet.PivotTables("PivotTable1").RowGrand = True
VBA - Fields
The columns are referred to as fields.
PivotFields
The PivotFields collection contains all the fields from the data source, inclusing any calculated fields
Each pivotfield has an orientation (column, row, page or data)
The orientation can also be set from the AddFields method.
Activesheet.PivotTables("-").PivotFields("-").CurrentPage = "(All)"
Activesheet.PivotTables("-").PivotFields("-").Orientation = xlHidden | xlRowField
Activesheet.PivotTables("-").PivotFields("-").Position = 3
There are several field collections
RowFields -
ColumnFields -
PageFields -
DataFields -
HiddenFields -
VisibleFields -
RowFields
can be added using AddField
ColumnFields -
can be added using AddField
Page Fields
can be added using AddField
DataFields
cannot be added using AddField
This collection contains the "sum of" fields
These do not appear in the PivotFields collection.
HiddenFields
VisibleFields
AddField Method
The AddFields method can contain multiple row, column and page fields.
This method can be used to add all types of fields except Data.
objPivotTable.AddFields( RowFIelds, ColumnFields, PageFields, AddToTable)
RowFields - can be a single name or an array of pivot field names Array("one", "two")
ColumnFields - can be a single name or an array of pivot field names Array("one", "two")
AddToTable - defaults to False which will replace all the existing fields. Change to True to add additional fields.
The AddToTable property is to specify whether to add the field(s) or to replace the existing field(s)
ActiveSheet.PivotTables(1).AddFields _
RowFields:="Name"
AddToTable:=True
You can use the Array function to include more than one field in a location
ActiveSheet.PivotTables(1).AddFields _
RowFields:=Array("Product","Name"), _
ColumnFields:="State", _
PageFields:="Date")
You must use the orientation method to add any calculated fields to your pivot table
ActiveSheet.PivotTables(1).PivotFields("Date").Orientation:=xlPageField
Set objField = ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer")
objField.Position = 1
objField.Orientation = xlPivotFieldOrientation.xlColumnField
objField.PivotItems("Q1").Position = 2
objField.Caption = ""
You can use the orientation and position properties to reorganise a pivot table.
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Customer").Function = xlCount
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Customer").Function = xlSum
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Customer").NumberFormat = "0"
You need to refer to the precise name of the field as it is displayed on the pivot table.
It is also possible to refer to a data field by its index number or by assigning it a different name ??
Subtotals
You can also change the display of individual pivot field totals
objPivotTable.PivotFields("name").Subtotals(icount) = False
There are 12 types of total and you must turn them all off ?
VBA - Ranges
ColumnRange
objPivotTable.ColumnRange
Returns the range that represents the column area.
DataBodyRange
objPivotTable.DataBodyRange
Returns the range that represents the data area.
RowRange
objPivotTable.RowRange
Returns the range that represents the row area.
DataLabelRange
objPivotTable.DataLabelRange
Returns the range that represents the labels for the data fields
This is read only
PageRange
objPivotTable.PageRange
Returns the range that represents the page area (smallest rectangle area)
PageRangeCells
objPivotTable.PageRangeCells
Returns the range that represents just the cells containing page related information (buttons, drop-downs)
This will probably be a discontinuous range.
TableRange1
objPivotTable.TableRange1
Returns the range that represents the entire pivot table (excluding the page fields)
TableRange2
objPivotTable.TableRange2
Returns the range that represents the entire pivot table (including the page fields)
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev