Sorting a pivot table

Question

I recorded this macro to sort a pivot table.

However, the pivot table is dynamic, and this macro only works if the pivot table ends in the correct column (k).

Is there a way to make it so the macro affects the last column with data in it?

On Error GoTo ErrorHandler
ErrorHandler:
 Resume Next
 Range("K4").Select
    ActiveSheet.PivotTables("InventoryPivotTable").PivotFields("Products"). _
        AutoSort xlAscending, "FBM ", ActiveSheet.PivotTables("InventoryPivotTable"). _
        PivotColumnAxis.PivotLines(8), 1
        
  ActiveWorkbook.ShowPivotTableFieldList = False
  Call highlightNegativeNumbersFBM

dumples · Accepted Answer · 2022-07-19 19:53:25Z

You probably don't even need the line that says "Select"

You also don't need to know that it is sorting by column k, it's really sorting by the 8th Pivot line which happens to be in K.

So if I were rewriting this line

    ActiveSheet.PivotTables("InventoryPivotTable").PivotFields("Products").       AutoSort xlAscending, "FBM ", ActiveSheet.PivotTables("InventoryPivotTable").        PivotColumnAxis.PivotLines(8), 1

dim lPivotTable as pivotTable
set lPivotTable = ActiveSheet.PivotTables("InventoryPivotTable") 

call LPivotTable.PivotFields("Products").AutoSort(xlAscending, "FBM ", lPivotTable.PivotColumnAxis.PivotLines(lPivotTable.PivotColumnAxis.PivotLines.Count), 1)

Besides shortening it, using the variable, I replaced the 8 with lPivotTable.PivotColumnAxis.PivotLines.Count which should equal 8 until you add or remove items

Collectives™ on Stack Overflow

Sorting a pivot table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related