List subgroups of pivottable vba

Question

I have a pivot table that looks like (i.e. there are three entries in the "ROWS" field in the user interface).

• Category
  • Subcategory
    • Sub Sub Category

I know that I can get all categories, subcategories, and subcategories by doing (in VBA) PT.PivotFields(3).PivotItems() , PT.PivotFields(2).PivotItems() and PT.PivotFields(1).PivotItems() respectively, where PT is my variable.

How do I know which subcategories are in each category, as well as for subcategories in the categories?

I tried using PT.PivotFields(3).PivotItems()(1).ChildItems() , but I get the error <Unable to get the ChildItems property of the PivotItem class> and the same for try ParentItem .

Any idea how I can do this?

An example of what I'm looking for. Take the pivot table below and list (in some way) that:

a has subcategories d, e; b has subcategories e, f; c has subcategories d, e, f; and that would be the same if there were several levels in the column position.

Answer 1

• Requirements: To create a table showing all combinations of Items for all RowFields and ColumnsFields for a given PivotTable .
  
  

• Solution: This can be achieved by setting some properties and methods of the pivot table and rows, columns and data fields as follows:

  • Set these pivot table properties:
    RowGrand, ColumnGrand, MergeLabels, RowAxisLayout

  • Set these properties for ColumnFields:
    Orientation

  • Set these properties for RowFields:
    RepeatLabels, Intermediate Results

  • Set these properties for 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 of the above properties to display the pivot table in a "table like" format, generating an array with a hierarchy of pivot table elements. It also provides options for clearing or not filtering the pivot table and turning on or off columns in the hierarchy.

  Options:
  Pt: Target PivotTable
  aPtHierarchy: An array output containing the hierarchy of the target pivot table.
  blClearFilters: Boolean. Determines whether to clear or turn off all pivot table filters. blIncludeCols: Boolean. Used to include or not include 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 get the PivotTable Hierarchy in Fig. 1. Note that some filters are applied in the pivot table.

  The PivotTable_Hierarchy_Rows_And_Columns procedure can be called as follows depending on the desired result:

   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 

  
  Picture 1
  
  
  Figure R1
  
  
  Figure R2
  
  
  Figure R3
  
  
  Figure R4
  
  

For more information about the resources used, see the following pages:

Summary Object (Excel)
PivotTable.RowAxisLayout Method (Excel)
PivotField Object (Excel)

Answer 2

I don’t know how many categories and subcategories you have, but you can put everything in several arrays (matrix) and then manage as you want.

Try the following:

 Sub GetCategories_SubCat() Dim PTable As PivotTable Dim matrix() As Variant Dim matrix_s() As Variant Dim msg$ Set PTable = ActiveSheet.PivotTables("PT") ReDim matrix(1 To PTable.RowFields.Count) For i = 1 To PTable.RowFields.Count matrix(i) = PTable.RowFields(i) For j = 1 To PTable.RowFields(i).PivotItems.Count ReDim matrix_s(1 To PTable.RowFields.Count, 1 To PTable.RowFields(i).PivotItems.Count) matrix_s(i, j) = PTable.RowFields(i).PivotItems(j) Next j Next i End Sub 

Answer 3

I think you can use the GetPivotData function to try to get a value for each set of rotation fields. If the error does not occur, you know that a certain category has a specific sub-item and sub-subcategory. Check out my sample code for a table with three levels:

 Sub ExaminePT() Dim pt As PivotTable Dim pf As PivotField Dim Lev1 As PivotField Dim Lev2 As PivotField Dim lev3 As PivotField Dim pi1 As PivotItem Dim pi2 As PivotItem Dim pi3 As PivotItem Dim checkVal As Double Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields If pf.Orientation = xlRowField Then Select Case pf.Position Case Is = 1 Set Lev1 = pf Case Is = 2 Set Lev2 = pf Case Is = 3 Set lev3 = pf End Select End If Next pf For Each pi1 In Lev1.PivotItems For Each pi2 In Lev2.PivotItems For Each pi3 In lev3.PivotItems On Error Resume Next checkVal = pt.GetPivotData("Incremental Benefits 2017", Lev1.Name, pi1.Name, _ Lev2.Name, pi2.Name, lev3.Name, pi3.Name) If Err.Number = 0 Then Debug.Print pi1 & "| " & pi2 & "| " & pi3 On Error GoTo 0 Next pi3 Next pi2 Next pi1 End Sub 

In the first cycle, I assign the variables to the categories, sub- and sub-subject, and then do what I suggested above. The results are written in the Immediate window, this is a sketch of the solution.

Probably, with a little effort, some can turn it into a more universal procedure for checking pivot tables for any number of nested levels.