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.

score 0 · Accepted Answer · answered Jul 11 '19 at 07:21

0

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

answered Jul 11 '19 at 07:21

Harvint Raj

69
1
11

You should avoid using [Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your Code. – Mikku Jul 11 '19 at 07:24
@Mikku do you have any other suggestions or way to make this better? – Harvint Raj Jul 11 '19 at 08:05
Just avoid using Select & Activate. As you are already referring to the correct objects. – Mikku Jul 11 '19 at 08:06

How do I select data from the excel pivot table?

1 Answers1