Read Pivot table source data

Hi Y’all,

I need a way to read the source data of a pivot table in excel.

Any ideas. Thanks for the help :smiley:

Hi @Cody_Barber ,
Have you tried with the Read Range activity?

HI,

This will read the data as it is displayed in excel, however, I want the data that feeds the Pivot Table.

Solution:

Sub GetPivotSourceData()
    Dim PT As PivotTable
    Set PT = ActiveSheet.PivotTables(1)
    PT.TableRange1.Cells(pT.TableRange1.Rows.Count, pT.TableRange1.Columns.Count).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = "READ THIS SHEET"
End Sub

(Use the above in a Invoke VBA activity)

The above expands the data in the pivot table saves it to a new sheet called “READ THIS SHEET” you can then use a read range for this created sheet.

If you want to do this more than once within the same excel file you will get an error. This is because there will be more than 1 sheet named “READ THIS SHEET”.

To Avoid this, you can use the following code after you have finished with reading the sheet,

Sub DeleteSheet()
    ActiveSheet.Delete
End Sub

(Use the above in a Invoke VBA activity)

This just deletes the sheet.

Hopefully this will help anyone else with a similar issue to myself

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.