How do I select data from the excel pivot table?

Question

Pivot Sheet

So for example when I click the cell G14 in the pivot sheet. It should display 9 data in another sheet say Sheet1. The number of data depends on the cells in column G of pivot sheet. I cant seem to find any code to get that data.

Sheet1

I need to to know how I can access that data using VBA.

Harvint Raj Harvint Raj · Accepted Answer · 2019-07-11T07:21:04

Sub SelectAndCopyPaste()
Dim lastrow() As String
Dim pt As PivotTable


Worksheets("Sheet1").Activate
ActiveSheet.Cells.Clear

With Worksheets("Pivot Sheet")
    .Activate
    Set pt = .PivotTables("PivotTable2")
    lastrow = Split(pt.RowRange.Address, ":")
    .Range(lastrow(1)).Offset(-1, 6).Select 'here is where you set which cell you want to choose
    Selection.ShowDetail = True
    Selection.Copy
End With

    Worksheets("Sheet1").Select
    ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues

End Sub

How do I select data from the excel pivot table?

1 Answers