Sort Pivot table based on subtotal

user_17794936 0
Viewed 57

When I search for PivotField.AutoSort method: https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.autosort, there is a Parameter called CustomSubtotal.

Anyone have the sample, what should I put in for this parameter?

Here the sample table sample table

The main objective is use vba to autosort PivotTable based on "C Total".

From PivotTable before sort to PivotTable after sort

The normal sort based on Grand Total is working.

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)

With pvt
 .PivotFields("Field 1").AutoSort Order:=Excel.XlSortOrder.xlDescending, Field:="Sum of Field 4"
End With

When adding in parameter CustomSubtotal, cannot work. I had tried both code below:

 .PivotFields("Field 1").AutoSort Order:=Excel.XlSortOrder.xlDescending, Field:="Sum of Field 4", CustomSubtotal:="C Total"

 .PivotFields("Field 1").AutoSort Order:=Excel.XlSortOrder.xlDescending, Field:="Sum of Field 4", CustomSubtotal:="C"

 .PivotFields("Field 1").AutoSort Order:=Excel.XlSortOrder.xlDescending, Field:="Sum of Field 4", CustomSubtotal:=1

Thanks.

Edited:

By the way, I had a not effective way to achieve the purpose. By using For Loop, loop through every row of PivotTable, then using GetPivotData.Value to compare the value, then exchange their Position if needed.

Somehow this method is wasting much time, as I had a long row of data to sort...

vba excel
1 Answers

Finally I found the solution myself...

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)
With pvt
 .PivotFields("Field 1").AutoSort Order:=Excel.XlSortOrder.xlDescending, Field:="Sum of Field 4", PivotLine:=ActiveSheet.PivotTables("PivotTable2").PivotColumnAxis.PivotLines(3), CustomSubtotal:=1
End With
user_177949360
Related