Extracting data from pivot table vba

Question

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

Sheets("Destination").Range("G" & NEWi) = PivotTable.GetPivotData(data_field, Range("I" & i), “Coverage”)

Which would be the proper way to write that line?

Replacing 'PivotTable' with 'PT' still gives error ' run time error 1004 application-defined or object-defined error' — L.Dutch, Feb 17 '17 at 14:13
@L.Dutch instead of `Looping` try `TableRange2` (see my code below) — Shai Rado, Feb 17 '17 at 14:52

R3uK · Accepted Answer · 2017-02-17T14:42:33.807

5

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!

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

edited Feb 17 '17 at 14:42

answered Feb 17 '17 at 13:45

R3uK

14,417
7
43
77

this gives "Run time error 438. Object doesn't support this property or method – L.Dutch Feb 17 '17 at 13:49
@L.Dutch : my bad there a typo! ;) – R3uK Feb 17 '17 at 13:50
1

@L.Dutch : What is the value of `data_field`? – R3uK Feb 17 '17 at 14:03
the data I have are given in the image attached to the question. i.e. for part1 I want to extract the sum of coverage 30 – L.Dutch Feb 17 '17 at 14:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135972/discussion-between-l-dutch-and-r3uk). – L.Dutch Feb 17 '17 at 14:18
@R3uK change your `With .TableRange1` to `With .TableRange2` – Shai Rado Feb 17 '17 at 14:23
@ShaiRado : Are you sure about this? Because I think that it that case, we don't need the page fields to be included. – R3uK Feb 17 '17 at 14:25

score 2 · Answer 2 · answered Feb 17 '17 at 14:38

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.

If the code below takes the wrong column, you can also use the Offset(0,1) to get the right one.

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

Extracting data from pivot table vba

2 Answers2