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.

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.

 

-------------------------------------------------------------------------------------------------------

 

Image 1

 

 

Example 1: Using the TableRange1 property - refer Image 1.

Worksheets("Sheet1").PivotTables("PivotTable1").TableRange1.Interior.Color = vbYellow

 

------------------------------------------------------------------------------------------------------- 

  

Image 2

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.

 

------------------------------------------------------------------------------------------------------- 

  

Image 3a

Example 3a: Using the PivotField.LabelRange Property - refer Image 3a.

Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("City").LabelRange.Interior.Color = vbYellow

 

------------------------------------------------------------------------------------------------------- 

 

Image 3b

Example 3b: Using the PivotField.LabelRange Property - refer Image 3b.

Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("City").LabelRange.Offset(2, 0).Interior.Color = vbYellow

------------------------------------------------------------------------------------------------------- 

 

Image 4

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.

 

------------------------------------------------------------------------------------------------------- 

 

Image 5

Example 5: Using the RowRange Property - refer Image 5.

Worksheets("Sheet1").PivotTables("PivotTable1").RowRange.Interior.Color = vbYellow 

-------------------------------------------------------------------------------------------------------

 

Image 6

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.

 

------------------------------------------------------------------------------------------------------- 

 

Image 7

Example 7: DataBodyRange - refer Image 7.

Worksheets("Sheet1").PivotTables("PivotTable1").DataBodyRange.Interior.Color = vbYellow

---

5. DataLabelRange Property

 

Returns a Range containing the data field labels in a PivotTable report.

------------------------------------------------------------------------------------------------------- 

 

Image 8

Example 8: DataLabelRange - refer Image 8.

Worksheets("Sheet1").PivotTables("PivotTable1").DataLabelRange.Interior.Color = vbYellow

 

---

 

6. PageRange Property

Returns a range that contains the page area in a PivotTable report.

------------------------------------------------------------------------------------------------------- 

 

Image 9

 

 

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.

 

-------------------------------------------------------------------------------------------------------

  

Image 10a

 

 

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

 

------------------------------------------------------------------------------------------------------- 

 

 

 

Image 10b

 

 

Example 10b: Return a range containing Items in a Row Field - refer Image 10b.

Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("City").DataRange.Interior.Color = vbYellow

 

------------------------------------------------------------------------------------------------------- 

 

 

 

Image 10c

 

 

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

 

------------------------------------------------------------------------------------------------------- 

 

  

Image 10d

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.

 

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.

 

 

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.

 

 

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.

 

 

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

 

---

 

 

 

 

9. Intersect Method

 

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.

 

 

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.

  

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

 source link：http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=154:referencing-an-excel-pivot-table-range-using-vba&catid=79&Itemid=475