Sorting a pivot table-CodePudding

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

CodePudding user response：

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