Excel VBA move pivot item to last position

Question

I have a pivot table with a lot of different countries in it, however, one of the groups is listed as "Other." The table should be sorted in decreasing order, with the exception of the "Other" entry, which should always come last. I've tried manually overwriting the position of "Other" after sorting in descending order, but every time I do so, the order of the entire table changes once more.

ActiveSheet.PivotTables(tblname).PivotFields("Country").AutoSort xlDescending, "Sum of Weight"

This works fine.

ActiveSheet.PivotTables(tblname).PivotFields("Country").PivotItems("Other").Position = 8

This moves "Other" into the last position but simultaneously changes the order of all other previously sorted items.

Answer 1

Try this:

Sub Tester()
    Dim pt As PivotTable, pf As PivotField
    
    Set pt = ActiveSheet.PivotTables(1) 'for example
    Set pf = pt.PivotFields("Country")
    pf.AutoSort xlDescending, "Sum of Weight"
    
    'pf.PivotItems("Other").Position = pf.PivotItems.Count 'problem
    MoveLast pf, "Other"
End Sub

'Move the item `piName` to the last position for pivotfield `pf`
Sub MoveLast(pf As PivotField, piName As String)
    Dim arr, i As Long, pi As PivotItem
    ReDim arr(1 To pf.PivotItems.Count)
    i = 0
    'store current field postions in an array, with the
    '  specified one at the end (if found)
    For Each pi In pf.PivotItems
        Select Case pi
            Case piName: arr(UBound(arr)) = pi
            Case Else:
                i = i + 1
                arr(i) = pi.Caption
        End Select
    Next pi
    'Debug.Print "------" & vbLf & Join(arr, vbLf)
    For i = 1 To UBound(arr)
        pf.PivotItems(arr(i)).Position = i
    Next i
End Sub