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 in Others by Kithuzzz
• 28,900 points
48 views

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 by narikkadan
• 53,520 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,630 points
300 views
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,630 points
153 views
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
• 53,520 points
516 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 53,520 points
242 views
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
• 53,520 points
281 views
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
533 views
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
2,415 views
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
• 22,960 points
117 views
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
• 53,520 points
460 views
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
• 53,520 points
1,188 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP