从数据透视表 vba 中提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42299433/
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
Extracting data from pivot table vba
提问by L.Dutch - Reinstate Monica
I have a pivot table to aggregate "coverage"on "part"only for accepted parts.
我有一个数据透视表汇总"coverage"上"part"仅接受部分。
I want then to extract the "sum of coverage"to another sheet.
I wrote the following macro:
然后我想将其提取"sum of coverage"到另一张纸上。我写了以下宏：
Sub Pull_data()
'Update the pivot table
Sheets("Pivot").PivotTables("PivotTable2").PivotCache.Refresh
'clear all filters
Sheets("Pivot").PivotTables("PivotTable2").PivotFields("Accepted").ClearAllFilters
'filters only accepted items
Sheets("Pivot").PivotTables("PivotTable2").PivotFields("Accepted").CurrentPage = "YES"
'get the last row of the pivot table
Set PT = Sheets("Pivot").PivotTables("PivotTable2")
With PT.TableRange1
lngLastRow = .rows(.rows.Count).Row
End With
For i = 4 To lngLastRow
'copy the coverage to destination sheet
NEWi = i + 10
Sheets("Destination").Range("G" & NEWi) = PivotTable.GetPivotData(data_field, Range("I" & i), “Coverage”)
Next i
End Sub
I get a run time error '424', object required on
我收到运行时错误“424”，需要对象
Sheets("Destination").Range("G" & NEWi) = PivotTable.GetPivotData(data_field, Range("I" & i), “Coverage”)
Which would be the proper way to write that line?
写那行的正确方法是什么？
采纳答案by R3uK
This should be :
这应该是：
Sheets("Destination").Range("G" & i + 10).Value = _
pT.GetPivotData("Sum of coverage", "Part", Range("I" & i).Value).Value
Because pT.GetPivotDatareturns a Range!
因为pT.GetPivotData返回一个范围！
Cleaned code :
清理代码：
Sub Pull_data()
Dim pT As PivotTable
Set pT = Sheets("Pivot").PivotTables("PivotTable2")
With pT
'''Update the pivot table
.PivotCache.Refresh
'''clear all filters
.PivotFields("Accepted").ClearAllFilters
'''filters only accepted items
.PivotFields("Accepted").CurrentPage = "YES"
'''get the last row of the pivot table
With .TableRange1
lngLastRow = .Rows(.Rows.Count).Row
For i = .Cells(2, 1).Row To lngLastRow
Debug.Print "i=" & i & "|" & Sheets("Pivot").Range("I" & i).Value
'''copy the coverage to destination sheet
Sheets("Destination").Range("G" & i + 10).Value = _
pT.GetPivotData("Sum of coverage", "Part", Sheets("Pivot").Range("I" & i).Value).Value
Next i
End With '.TableRange1
End With 'pT
End Sub
回答by Shai Rado
You could try copying the entire Column from your PivotTableafter it's filtered to your needs, with TableRange2, use the Resizeto a single column, and then Copyand PasteSpecial xlValuesto the destination worksheet.
您可以尝试在PivotTable根据您的需要过滤后从您的整个 Column 复制整个 Column ，使用TableRange2，使用Resize到单个列，然后Copy和PasteSpecial xlValues到目标工作表。
If the code below takes the wrong column, you can also use the Offset(0,1)to get the right one.
如果下面的代码使用了错误的列，您也可以使用Offset(0,1)来获得正确的列。
With PT
.TableRange2.Resize(.TableRange2.Rows.Count, 1).Copy
Worksheets("Destination").Range("G14").PasteSpecial xlValues '<-- start Pasting from Row 14
End With
Note: if the code above takes the column to the left, try the code line below:
注意：如果上面的代码将列移到左边，请尝试下面的代码行：
.TableRange2.Resize(.TableRange2.Rows.Count, 1).Offset(, 1).Copy
