vba 如何使用VBA将数据透视表中的一列移动到末尾？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19485644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to move one column to the end in Pivot table with VBA?
提问by JennyZheng
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.
我创建了一个 pirvot 表，但列顺序不是我想要的。我想将一列移到右端。我可以用 Excel 来做，但不知道如何用 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.
在Excel中，可以这样做--激活要移动的列标题，单击右侧按钮，选择移动，移动“--”结束。
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.
录制宏——录制宏时，它只显示确切位置，就像 ActiveSheet.PivotTables("PivotTable16").PivotFields("wk").PivotItems("#VALUE!") _ .Position = 12 但我想要要在其他文件中使用宏，可能结束位置不是12。那么宏就不能使用了。
Hope can get help here. How to do it to move the colunm to end by VBA? Thanks.
希望能在这里得到帮助。如何通过VBA将列移动到结尾？谢谢。
回答by sam092
I hope I get you right.
我希望我能理解你。
You can get the end position by
您可以通过以下方式获得最终位置
ActiveSheet.PivotTables("PivotTable1").PivotFields("whatever").PivotItems.count
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
回答by TechnoWolf
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.
只是对遇到此问题并遇到与我相同的问题的任何人的随机说明。如果在你的宏中你隐藏了字段，它会给你一个错误。不要将此代码用作设置数据透视表时所做的最后一件事。使用此代码，让它移动到底部位置，然后让您的代码隐藏您的字段。如果您先隐藏字段，它会计算隐藏字段，然后尝试将其移到数据透视表之外并导致错误。
