User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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

5. DataLabelRange Property

6. PageRange Property

7. PivotField.DataRange Property and PivotItem.DataRange Property

8. PivotTable.PivotSelect Method

9. Intersect 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

 


 

 

5. DataLabelRange Property

 

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

 


 

 

6. PageRange Property

 

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

 


 

 

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.

 

 

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

 

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