CODEWITHSUNDEEP
vbaexcel
kulapo
kulapo

Reputation: 397

Custom Sort on Pivot Items (rowfield)

I was thinking for this for a while now for the correct loop to be use here.

I'm trying to move/sort those fruits in the bottom of the pivot, however they should be in that exact particular order in the bottom part (fruit1, fruit2,..., apples, oranges, grapes).

However, there's a chance that one of those in bottom 3 fruits may not present in the data so i can't subtract its position from the PivotItems.Count.

With pvt

   .PivotField("fruits").Orientation = xlRowField
   .RowAxisLayout xlTabularRow

   For Each pi In .PivotFields("fruits").PivotItems
      Select Case LCase(pi.Name)
         Case "apples", "oranges", "grapes"
            .PivotFields("fruits").PivotItems(pi.Name).Position = .PivotFields("fruits").PivotItems.Count
      End Select
   Next pi

End with

Upvotes: 1

Views: 1705

Answers (1)

QHarr
QHarr

Reputation: 84465

You will need to adjust this to suit your needs but the following will order your list. It assumes you data is laid out as follows in sheet2:

Data layout

And it creates a pivot in Sheet7

Pivot Layout

Option Explicit
Public Sub CreateOrderedFruitPivot()

    Dim pc As PivotCache, pt As PivotTable, pi As PivotItem
    Dim varSubsOff, varItemList, varItem
    varSubsOff = Array(False, False, False, False, False, False, False, False, False, False, False, False)
  
    With ActiveSheet 'better to used named Sheet

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                          "'Sheet2'!R1C1:R4C2", Version:=6).CreatePivotTable TableDestination:="Sheet7!R3C1", _
                                          TableName:="PivotTable2", DefaultVersion:=6

        Set pt = ThisWorkbook.Worksheets("Sheet7").PivotTables("PivotTable2")

        With pt
 
            .AddDataField .PivotFields("fruits"), "Count of fruits", xlCount
            
            With .PivotFields("fruits")
                .Orientation = xlRowField
                .Position = 1
            End With

            ActiveWorkbook.ShowPivotTableFieldList = False
        
            With .PivotFields("fruits")
                .PivotItems("(blank)").Visible = False
                varItemList = Array("Apples", "Oranges", "Grapes")
                
                On Error Resume Next       
                For Each varItem In varItemList
                    Set pi = .PivotItems(varItem)
                    pi.Visible = True
                    pi.Position = .VisibleItems.Count
                    Set pi = Nothing
                Next varItem
                On Error GoTo 0
            End With
                     
            pt.RowAxisLayout xlTabularRow
        End With

    End With

End Sub

You will need to amend to your sheets and ranges accordingly (also check field names) but the part you are really interested in is the following:

 With .PivotFields("fruits")

    .PivotItems("(blank)").Visible = False
    
    varItemList = Array("Apples", "Oranges", "Grapes")
    
    On Error Resume Next
    
    For Each varItem In varItemList
        Set Pi = .PivotItems(varItem)
        Pi.Visible = True
        Pi.Position = .VisibleItems.Count
        Set Pi = Nothing
    Next varItem
    
    On Error GoTo 0
End With

Here you are looping an array in the order you want to see items and adding them and the error handling deals with the item not being present.

Credits go to Rory here: Sorting pivot items as i used his solution as a framework for approaching your problem.

Upvotes: 2

Related Questions