I have a pivot table structure that looks like (i.e. there are three entries in the "ROWS" box in the pivottable UI)
I know that I can get all the Categories, subcategories, and sub-sub categories by doing (in VBA) PT.PivotFields(3).PivotItems()
, PT.PivotFields(2).PivotItems()
and PT.PivotFields(1).PivotItems()
respectively, where PT is my pivottable.
How can I find out which subcategories are in each category, and same for sub sub categories in categories?
I tried using PT.PivotFields(3).PivotItems()(1).ChildItems()
but I get an error <Unable to get the ChildItems property of the PivotItem class>
and same for trying ParentItem
.
Any idea how I can do this?
An example of what I am looking for. Take the pivot table below, and enumerate (in some way) that:
a has subcategories d,e; b has subcategories e,f; c has sub categories d,e,f; and it would be the same if there were multiple levels on the columns position.
Requirement:
To build a table showing all the Items
combinations for all RowFields
and ColumnsFields
of a given PivotTable
.
Solution: This can be achieved by setting some of the properties and methods of the PivotTable and the Row, Column and Data Fields as follows:
Set these PivotTable properties:
RowGrand, ColumnGrand, MergeLabels, RowAxisLayout
Set these properties for the ColumnFields:
Orientation
Set these properties for the RowFields:
RepeatLabels, Subtotals
Set these properties for the DataFields:
Orientation
Procedure:
Sub PivotTable_Hierarchy_Rows_And_Columns(pt As PivotTable, _
aPtHierarchy As Variant, blClearFilters As Boolean, blIncludeCols As Boolean)
This procedure adjusts all the above-mentioned properties in order to display the PivotTable in a “table like” format generating an array with the PivotTable’s Hierarchy. It also provides the options to clear or not the PivotTable filters and to include or not the ColumnFields in the hierarchy.
Parameters:
Pt: Target PivotTable
aPtHierarchy: Array output containing the hierarchy of the target PivotTable.
blClearFilters: Boolean. Determines whether to clear or not the all PivotTable filters.
blIncludeCols: Boolean. Used to include or not the ColumnFields in the output hierarchy.
Syntax:Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, blClearFilters, blIncludeCols)
VBA:
Sub PivotTable_Hierarchy_Rows_And_Columns(pt As PivotTable, _
aPtHierarchy As Variant, blClearFilters As Boolean, blIncludeCols As Boolean)
Dim pf As PivotField
Rem PivotTable Properties & Methods
With pt
.RowGrand = False
.ColumnGrand = False
.MergeLabels = False
.RowAxisLayout xlTabularRow
If blClearFilters Then .ClearAllFilters
End With
Rem ColumnFields Properties
For Each pf In pt.ColumnFields
If blIncludeCols Then
pf.Orientation = xlRowField
Else
pf.Orientation = xlHidden
End If: Next
Rem RowFields Properties
For Each pf In pt.RowFields
With pf
On Error Resume Next
.RepeatLabels = True
.Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
On Error GoTo 0
End With: Next
Rem DataFields Properties
For Each pf In pt.DataFields
pf.Orientation = xlHidden
Next
Rem Set Hierarchy Array
aPtHierarchy = pt.RowRange.Value2
End Sub
Example:
Assuming we need to obtain the Hierarchy of the PivotTable in fig. 1. Note that the PivotTable has some filters applied.
The procedure PivotTable_Hierarchy_Rows_And_Columns
can be called as follows depending on the required outcome:
Sub PivotTable_Hierarchy_Rows_And_Columns_TEST()
Dim pt As PivotTable, aPtHierarchy As Variant
'Set PivotTable - Change worksheet and pivottable name as required
Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("PtTst")
'1. To obtain the Hierarchy for Rows and Columns, clearing all the filters applied to the PivotTable try this:
Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, True, True) 'See results in Fig. R1 (Table & Array)
‘2. To obtain the Hierarchy for Rows only, clearing all the filters applied to the PivotTable try this:
Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, True, False) 'See results in Fig. R2 (Table & Array)
'3. To obtain the Hierarchy for Rows and Columns with the filters currently applied to the PivotTable try this:
Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, False, True) 'See results in Fig. R3 (Table & Array)
'4. To obtain the Hierarchy for Rows only with the filters currently applied to the PivotTable try this:
Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, False, False) 'See results in Fig. R4 (Table & Array)
End Sub
Fig. 1
Fig. R1
Fig. R2
Fig. R3
Fig. R4
For additional information on the resources used see the following pages:
PivotTable Object (Excel)
PivotTable.RowAxisLayout Method (Excel)
PivotField Object (Excel)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With