Excel VBA move pivot item to last position

0 votes

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.

Jan 14, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
answered Jan 14, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP