I have a workbook that has multiple pivot tables calculating a % of compliance based on rows of data. Every time I update the rows of data and refresh the pivot tables the % of compliance will change. I want the pivot tables to be sorted from smallest % to largest %. Currently, I have click on every pivot table and put it in sort order every time the workbook is refreshed.

Is there a way to set the pivot tables to ALWAYS sort the value A to Z when being refreshed?


If you want to Automate the Refresh & Sort process for all the Pivot Tables, whenever ENTER or UPDATE Data, the below written VBA code can help you to do so.

    Private Sub Worksheet_Change(ByVal Target As Range)
     Dim Wrkst As Excel.Worksheet
     Dim PvtBL As Excel.PivotTable

     For Each Wrkst In Application.ActiveWorkbook.Worksheets
        For Each pvtBL In Wrkst.PivotTables
            ActiveWorkbook.PivotTables().PivotFields("Sales").AutoSort _
                xlAscending, ActiveSheet.PivotTables(). _
        Next pvtBL
    Next Wrkst
 End Sub

NB: Since Field Name is not specified therefore I'm assuming Sales as Field to Sort, you can change it as and what you need.

Hope this help you.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.