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
– L.Dutch, Commented Feb 17, 2017 at 14:13
@L.Dutch instead of Looping try TableRange2 (see my code below) — Shai Rado
– Shai Rado, Commented Feb 17, 2017 at 14:52

R3uK · Accepted Answer · 2017-02-17 14:42:33Z

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, 2017 at 14:42

answered Feb 17, 2017 at 13:45

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

L.Dutch Over a year ago

this gives "Run time error 438. Object doesn't support this property or method

R3uK Over a year ago

@L.Dutch : my bad there a typo! ;)

R3uK Over a year ago

@L.Dutch : What is the value of data_field?

L.Dutch Over a year ago

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 Over a year ago

Let us continue this discussion in chat.

|

Shai Rado · Accepted Answer · 2017-02-17 14:38:09Z

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.

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

answered Feb 17, 2017 at 14:38

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Extracting data from pivot table vba

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related