none
VBA PivotField Ordering Issue RRS feed

  • Question

  • I have two different PivotFields, each containing a number of PivotItems.  They look a bit like this when they are separated:

     

    Categories
         Category1
         Category2
         Category3
    
    Results
         Result1
         Result2
         Result3
         Result4
         Result5
         Result6

     


    When they're put together, the ordering is changed to look a bit like this instead, with one PivotField appearing as a child of the other:

    Category1
         Result2
         Result5
    
    Category2
         Result4
         Result1
    
    Category3
         Result3
         Result6


    When I try to grab the output of the PivotFields("Results").PivotItems, I receive it in the original order (1, 2, 3, 4, 5, 6).

     

    How do I retrieve the results as they appear in the Pivot Table (2, 5, 4, 1, 3, 6)?  Thanks for any help or advice!

    Wednesday, October 5, 2011 5:48 PM

Answers

  • Under Pivot Table Options, select the Display tab, and enable Classic view. Then turn off subtotals, and your row  labels will be on one row, not two. Then try the code. Do you have data anywhere in the table?


    HTH, Bernie
    • Marked as answer by FryCook50 Thursday, October 6, 2011 6:36 PM
    Thursday, October 6, 2011 5:43 PM

All replies

  • Try it this way. I have assumed that you only have one data field.
    Sub PivotTableReading()
        Dim PT As PivotTable
        Dim PI As PivotItem
    
        Dim myC As Range
        Dim r As Range
    
        Set PT = ActiveSheet.PivotTables(1)
        For Each PI In PT.PivotFields("Categories").PivotItems
            Set myC = Intersect(PI.DataRange.EntireRow, PT.PivotFields("Results").DataRange)
            For Each r In myC
                MsgBox PI.Value & ", " & r.Value & ": " & _
                    Intersect(r.EntireRow, PI.DataRange).Value
    Next r Next PI End Sub


    HTH, Bernie
    Thursday, October 6, 2011 1:45 PM
  • Thanks for the reply, Bernie!  I've tried out your code as given, and also modified it a few times to fit the table, but keep getting the same result.  Each time I run the loop, the output given is "Category1, Category1, 0".

    It may be worth clarifying that these are two fields of text, both residing within the Row area.  If I look at only the Results PivotField, all of the items are out of the regular order and no longer make sense, but when they are combined with the Categories Pivotfield in the table, they're arranged correctly in chronological order.  I'm not sure how they were put into this table that they would behave in this way, but I've got to get them into the correct order somehow!  :-p


    • Edited by FryCook50 Thursday, October 6, 2011 5:13 PM
    Thursday, October 6, 2011 5:01 PM
  • Under Pivot Table Options, select the Display tab, and enable Classic view. Then turn off subtotals, and your row  labels will be on one row, not two. Then try the code. Do you have data anywhere in the table?


    HTH, Bernie
    • Marked as answer by FryCook50 Thursday, October 6, 2011 6:36 PM
    Thursday, October 6, 2011 5:43 PM
  • That did it!  Is there an easy way to automate this and then reverse it in VBA so that the end user won't notice the changes?
    Thursday, October 6, 2011 6:36 PM
  •     Application.ScreenUpdating = False
       
        With ActiveSheet.PivotTables(1)
            .InGridDropZones = True
            .RowAxisLayout xlTabularRow
        End With

        'other stuff

        With ActiveSheet.PivotTables(1)
            .InGridDropZones = False
            .RowAxisLayout xlOutlineRow
        End With

       'And you may need this:

        With ActiveSheet.PivotTables(1).PivotFields("Categories")
            .LayoutForm = xlOutline
            .LayoutCompactRow = True
        End With

        
        Application.ScreenUpdating = True


    HTH, Bernie
    Thursday, October 6, 2011 7:00 PM