从数据透视表vba中提取数据

I have a pivot table to aggregate "coverage" on "part" only for accepted parts.

我有一个数据透视表，只为已接受的部分聚合“部分”上的“覆盖”。

I want then to extract the "sum of coverage" to another sheet. I wrote the following macro:

我希望然后将“覆盖总和”提取到另一张表。我写了以下宏：

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?

这是写这条线的正确方法吗？

2 个解决方案

#1

This should be :

这应该是：

Sheets("Destination").Range("G" & i + 10).Value = _
    pT.GetPivotData("Sum of coverage", "Part", Range("I" & i).Value).Value

Because pT.GetPivotData returns a Range!

因为pT.GetPivotData返回一个Range！

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

#2

You could try copying the entire Column from your PivotTable after it's filtered to your needs, with TableRange2 , use the Resize to a single column, and then Copy and PasteSpecial xlValues to the destination worksheet.

您可以尝试在根据需要过滤后，从数据透视表中复制整个列，使用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.

如果下面的代码采用了错误的列，您也可以使用偏移量（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

#1