如何用VBA按指定总计列'Total Sum of RevVarLY'排序数据透视表？

Let's sort out why your current code isn't working first: the Field parameter in AutoSort expects the original name of the data field, not the display name of the total column (Total Sum of RevVarLY). Excel generates that total label by combining the aggregation method (Sum) with the data field name, but the actual field name VBA needs is just RevVarLY.

Here are two reliable ways to get the sorting working:

Method 1: Explicitly Reference Data Field (Recommended) #

This approach makes your code clearer and less prone to errors if your pivot table structure changes:

Dim pt As PivotTable
Dim rowField As PivotField
Dim dataField As PivotDataField

' Set references to your pivot table, row field, and data field
Set pt = ThisWorkbook.Worksheets("3").PivotTables("PivotTable3")
Set rowField = pt.PivotFields("RevVarLY") ' This is the row/column field you want to sort
Set dataField = pt.DataFields("RevVarLY") ' The data field used for the total sum

' Sort the row field in descending order based on the data field's values
rowField.AutoSort Order:=xlDescending, Field:=dataField.Name, PivotTable:=pt

Method 2: Simplified Direct Call #

If you prefer a shorter version, you can directly use the data field's original name instead of the total column display name:

With ThisWorkbook.Worksheets("3").PivotTables("PivotTable3")
    .PivotFields("RevVarLY").AutoSort _
        Order:=xlDescending, _
        Field:="RevVarLY", _
        PivotTable:=.PivotTables("PivotTable3")
End With

Key Notes to Verify: #

• Make sure PivotFields("RevVarLY") refers to a row or column field (not a data field) in your pivot table—this is the field whose items you want to reorder.
• Confirm that your pivot table has a data field named RevVarLY with a Sum aggregation (since the total column is labeled "Total Sum of RevVarLY").

内容的提问来源于stack exchange，提问作者Sorath