Excel VBA Prevent PivotTable re-sorting upon change

Question

I have a pivot that I want to NOT re-sort after changing a filter. This would replicate in code the effect of right clicking the sorted field's drop-down arrow and going to More Options and unchecking the box for "Sort automatically every time the report is updated". I tried the macro recorder, but it didn't show anything.

I am sorting the pivot off a total including all options. I then want the order of my Rows to stay the same as I exclude/include certain options. Setting PivotFields.Autosort=xlManual just seemed to turn off and undo the sorting I do want. The line of code below is how I currently am sorting it. This works, I just want it to not keep autosorting.

PivotTables("PivotTable1").PivotFields("Category").AutoSort xlDescending, "Count of Category"

Example: If my original table is this when I filter for "A":

When I change the filter to now filter for "B", I want the Categories (rows in the pivot) to stay in the same order, and not re-sort automatically by the Total. So it would look like this:

Michal · Accepted Answer · 2025-06-27 00:45:45Z

2

You just need to disable automatic sorting, or in VBA terms, set it to manual:

Sub SortPivotWithoutAutoResorting()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PivotTable1")

    'Automatically sort the pivot table
    pt.PivotFields("Category").AutoSort xlDescending, "Count of Category"
    
    'Turn off auto-sorting for future updates
    pt.PivotFields("Category").AutoSort xlManual, "Count of Category"
End Sub

answered Jun 27 at 0:45

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bluebonnet Jun 27 at 13:25

When I implement that code, turning the AutoSort to Manual causes the pivot to lose the Descending sort on Count of Category, and it sorts instead on the Rows (Categories) alphabetically. Is there something else I am missing? I'd like to preserve the order of the pivot rows from the Descending sort through any pivot changes.

Collectives™ on Stack Overflow

Excel VBA Prevent PivotTable re-sorting upon change

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related