vba 将数据从数据透视表复制到新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26486734/
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
Copy Data from Pivot table to new sheet
提问by 4d554d424c4553
Hi Have a VBA script that very simply copy's data from my totals in the pivot table and past them into another sheet. Code:
嗨，有一个 VBA 脚本，可以非常简单地从数据透视表中的总计中复制数据，然后将它们传递到另一个工作表中。代码：
Public Sub Update_Tracker_TS_Custody()
Sheets("TS_Custody_Pivot").Select
Sheets("TS_Custody_Pivot").Range("B05:C05").Copy
Sheets("TS_Custody_Tracking").Cells(Rows.Count, "C").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("TS_Custody_Tracking").Select
n = Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & n) = Date
Range("B" & n) = Time End Sub
My issues is that my Pivot table states can change so one day i could have 5 different state totals and another with only 4 in a different order from when it was 5 States. So when the VBA script copy's onto the new sheet it dose not line up correctly under all the states
我的问题是我的数据透视表状态可以改变，所以有一天我可以有 5 个不同的状态总数，而另一个只有 4 个，与 5 个状态时的顺序不同。因此，当 VBA 脚本复制到新工作表上时，它不会在所有状态下正确排列
e.g. Day 1
例如第 1 天
State 1 State 2 State 3 State 4 State 5
状态 1 状态 2 状态 3 状态 4 状态 5
Day 2
第 2 天
State 1 State 2 State 3 State 5
状态 1 状态 2 状态 3 状态 5
How can i get the VBA script to copy and match so if state 4 is missing it will still copy 1,2,3,5 under the right column the other sheet and put a 0 in state 4.
我怎样才能让 VBA 脚本复制和匹配，所以如果状态 4 丢失，它仍然会在另一张表的右列下复制 1、2、3、5，并在状态 4 中放置一个 0。
回答by 4d554d424c4553
I have been able to sort this my self by simply created another table from the pivot table using
我已经能够通过简单地使用数据透视表从数据透视表创建另一个表来对自己进行排序
=IFERROR(GETPIVOTDATA("State",$A,"State","New"),0)
and then using the macro to copy the data from the table
然后使用宏从表中复制数据
