Referencing an Excel Pivot Table Range using VBA
This section explains how to access, reference, select or use a specific part of a Pivot Table, be it the Field or Data Labels, Row or Column Ranges, the Data or Values Area, the Page Area, specified cells or range in a PivotTable report or the entire PivotTable itself.
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
-----------------------------------------------------------------------------------------
Contents:
1. TableRange1 Property and TableRange2 Property
2. PivotField.LabelRange Property and PivotItem.LabelRange Property
3. RowRange Property and ColumnRange Property
4. PivotTable.DataBodyRange Property
7. PivotField.DataRange Property and PivotItem.DataRange Property
8. PivotTable.PivotSelect Method
-----------------------------------------------------------------------------------------
1. TableRange1 Property and TableRange2 Property
TableRange1 Property and TableRange2 Property return a Range - in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
-------------------------------------------------------------------------------------------------------
Example 1: Using the TableRange1 property - refer Image 1.
Worksheets("Sheet1").PivotTables("PivotTable1").
TableRange1.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 2: Using the TableRange2 property - refer Image 2.
Worksheets("Sheet1").PivotTables("PivotTable1").
TableRange2.Interior.Color = vbYellow
2. PivotField.LabelRange Property and PivotItem.LabelRange Property
PivotField.LabelRange Property - returns the range that contains the Field Label. PivotItem.LabelRange Property - returns all cells that contain the Item Label in a Field.
-------------------------------------------------------------------------------------------------------
Example 3a: Using the PivotField.LabelRange Property - refer Image 3a.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").LabelRange.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 3b: Using the PivotField.LabelRange Property - refer Image 3b.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").LabelRange.Offset(2, 0).Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 4: Using the PivotItem.LabelRange Property - refer Image 4.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").PivotItems("London").
LabelRange.Interior.Color = vbYellow
3. RowRange Property and ColumnRange Property
RowRange Property returns a Range containing the row area in a PivotTable report. ColumnRange Property returns a Range containing the column area in a PivotTable report.
-------------------------------------------------------------------------------------------------------
Example 5: Using the RowRange Property - refer Image 5.
Worksheets("Sheet1").PivotTables("PivotTable1").
RowRange.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 6: Using the ColumnRange Property - refer Image 6.
Worksheets("Sheet1").PivotTables("PivotTable1").
ColumnRange.Interior.Color = vbYellow
4. PivotTable.DataBodyRange Property
Returns a Range containing the data area or values in a PivotTable report.
-------------------------------------------------------------------------------------------------------
Example 7: DataBodyRange - refer Image 7.
Worksheets("Sheet1").PivotTables("PivotTable1").
DataBodyRange.Interior.Color = vbYellow
Returns a Range containing the data field labels in a PivotTable report.
-------------------------------------------------------------------------------------------------------
Example 8: DataLabelRange - refer Image 8.
Worksheets("Sheet1").PivotTables("PivotTable1").
DataLabelRange.Interior.Color = vbYellow
Returns a range that contains the page area in a PivotTable report.
-------------------------------------------------------------------------------------------------------
Example 9: Refer Image 9.
Worksheets("Sheet1").PivotTables("PivotTable1").
PageRange.Interior.Color = vbYellow
7. PivotField.DataRange Property and PivotItem.DataRange Property
PivotField.DataRange Property returns a Range containing: (i) Data in the Data Field; (ii) Items in a Row, Column or Page Field; and (iii) Data in the Item. PivotItem. DataRange Property returns a Range containing Data in a PivotItem.
-------------------------------------------------------------------------------------------------------
Example 10a: Return a range containing Data in the Data Field - refer Image 10a.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("Sum of Sales").DataRange.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 10b: Return a range containing Items in a Row Field - refer Image 10b.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").DataRange.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 10c: Return a range containing Data in a PivotItem - refer Image 10c.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").PivotItems("Paris").
DataRange.Interior.Color = vbYellow
-------------------------------------------------------------------------------------------------------
Example 10d: Offset DataRange - refer Image 10d.
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").DataRange.Cells(1).Interior.Color = vbYellow
8. PivotTable.PivotSelect Method
A part of the PivotTable report can be selected using this method. This method has 3 arguments - Name, Mode & UseStandardName. Name argument is mandatory to specify while other arguments are optional. Name argument represents the part of the report to be selected. Mode specifies the selection mode for type of items to be selected - options are xlBlanks, xlButton, xlDataAndLabel, xlDataOnly, xlFirstRow, xlLabelOnly and xlOrigin viz. to select labels use xlLabelOnly.
-------------------------------------------------------------------------------------------------------
Example 11a: PivotField - xlLabelOnly. Refer Image 11a.
Sub PivotTableRange11a()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Worksheets("Sheet1").Activate
Application.PivotTableSelection = True
PvtTbl.PivotSelect "City", xlLabelOnly
Selection.Interior.Color = vbYellow
End Sub
-------------------------------------------------------------------------------------------------------
Example 11b: PivotField - xlDataAndLabel. Refer Image 11b.
Sub PivotTableRange11b()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Worksheets("Sheet1").Activate
Application.PivotTableSelection = True
PvtTbl.PivotSelect "City", xlDataAndLabel
Selection.Interior.Color = vbYellow
End Sub
-------------------------------------------------------------------------------------------------------
Example 11c: DataField - xlDataOnly. Refer Image 11c.
Sub PivotTableRange11c()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Worksheets("Sheet1").Activate
Application.PivotTableSelection = True
PvtTbl.PivotSelect "Sum of Sales", xlDataOnly
Selection.Interior.Color = vbYellow
End Sub
-------------------------------------------------------------------------------------------------------
Example 11d: PivotItem - xlDataOnly. Refer Image 11d.
Sub PivotTableRange11d()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Worksheets("Sheet1").Activate
Application.PivotTableSelection = True
PvtTbl.PivotSelect "New York", xlDataOnly
Selection.Interior.Color = vbYellow
End Sub
Returns a range at the intersection of two or more ranges. It enables selection of specified cells or range in a PivotTable report.
-----------------------------------------------------------------------------------------------
Example 12a: Intersect method - refer Image 12a.
Sub PivotTableRange12a()
Dim PvtTbl As PivotTable
Dim rng1 As Range
Dim rng2 As Range
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Set rng1 = PvtTbl.PivotFields("Sum of Budgeted Sales").
DataRange
Set rng2 = PvtTbl.PivotFields("Car Models").
PivotItems("MidSize").DataRange.EntireRow
Intersect(rng1, rng2).Interior.Color = vbYellow
End Sub
-------------------------------------------------------------------------------------------------------
Example 12b: Intersect method - refer Image 12b.
Sub PivotTableRange12b()
Dim PvtTbl As PivotTable
Dim rng1 As Range
Dim rng2 As Range
Dim rng As Range
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Set rng1 = PvtTbl.DataBodyRange
Set rng2 = PvtTbl.PivotFields("Car Models").
PivotItems("MidSize").DataRange.EntireRow
If Intersect(rng1, rng2) Is Nothing Then
MsgBox "No Range Intersects"
Exit Sub
End If
For Each rng In Intersect(rng1, rng2)
If rng.value >= 3500 Then
rng.Interior.Color = vbYellow
End If
Next
End Sub
----------------------------------------------------------------------------------------------------
