How to select a pivot table data range from another sheet

Question

New to VBA. I simply trying to figure out why I cant select the first rows data Range from a pivot table from another sheet. Any ideas?

Sheet name with pivot table = Pivot Pivot table name = PivotTable3

Dim pt As PivotTable Set pt = Sheets("Pivot").PivotTables("PivotTable3")

Worksheets("Pivot").Activate
pt.PivotFields("Row Labels").DataRange.Select
Selection.Copy

I thought this would select the first Row for me. However I am met with error "1004"

FXD FXD · Accepted Answer · 2019-02-12T15:42:06

Not sure exactly what range you want. Try one of those:

Worksheets("Pivot").PivotTables("PivotTable3").TableRange1.Rows(1)
Worksheets("Pivot").PivotTables("PivotTable3").TableRange2.Rows(1)
Worksheets("Pivot").PivotTables("PivotTable3").DataBodyRange.Rows(1)
Worksheets("Pivot").PivotTables("PivotTable3").RowRange.Rows(1)

Or maybe something like Application.Intersect(Worksheets("Pivot").PivotTables("PivotTable3").TableRange1, Worksheets("Pivot").PivotTables("PivotTable3").DataBodyRange.Rows(1).EntireRow)

BTW, the following approach is to be banned (and actually, all people with experience on VBA will avoid it):

SomeSheet.Activate
MyRange.Select
Selection.DoSomeAction

Instead, simply do MyRange.DoSomeAction.

This will be smoother with no blink on the screen.

How to select a pivot table data range from another sheet

1 Answers