How to move one column to the end in Pivot table with VBA?

Question

I create a pirvot table, but the colunm order is not what I wanted. I want to move one column to right end. I can do it in Excel, but don't know how to do it with VBA.

In Excel, can do in this way--active the column header which you want to move, click right-hand button, choose Move, Move "--" to end.

Record Macro--when record Macro, it just shows the exact position, just like, ActiveSheet.PivotTables("PivotTable16").PivotFields("wk").PivotItems("#VALUE!") _ .Position = 12 But I want to use the Macro in other files, maybe the end position is not 12. Then the Macro can't be used.

Hope can get help here. How to do it to move the colunm to end by VBA? Thanks.

Answer 1

I hope I get you right.

You can get the end position by

ActiveSheet.PivotTables("PivotTable1").PivotFields("whatever").PivotItems.count

Basically it returns the number of items for a label

Edited

So you could do like

Dim total as Integer 

total = ActiveSheet.PivotTables("PivotTable1").PivotFields("whatever").PivotItems.count

ActiveSheet.PivotTables("PivotTable1").PivotFields("whatever").PivotItems("whatever").position = total

Answer 2

Just a random note to anyone that comes across this and runs into the same issue I did. If in your macro you're hiding fields, it's going to give you an error. Don't use this code as the last thing you do when setting up your pivot table. Use this code, let it move to the bottom position, THEN have your code to hide your fields. If you hide fields first, it'll count the hidden fields then try to move it outside your pivot table and cause an error.