Copy and paste data from columns A to AI up to the last row in pivot table

Question

I'm trying to copy and paste data from columns A and AI up to the last populated row in a pivot table that is located in columns AO to AP.

I have tried this code but it keeps pasting beyond the last row of the pivot table.

Sub Test1()

    Dim LastRow As Long

    Set blah = Range("A2:AI2")

    Sheets("FI").Activate

    LastRow = Cells(Cells.Rows.Count, "AO").End(xlUp).Row
    Range("A2:AI2" & LastRow).FillDown

End Sub

You are using an unreliable way of getting the "last row", see here: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — David Zemens, Mar 23 '16 at 18:26

score 0 · Answer 1 · answered Mar 23 '16 at 19:23

To select a field from a pivot table in VBA, you could use something like this.

Dim Wks as Worksheet, oPvt as PivotTable
Set Wks = ActiveWorksheet
Set oPvt = Wks.PivotTables([PivotTableName])

oPvt.PivotSelect Name:="[Field]", _
                    Mode:=xlDataAndLabel, _
                    UseStandardName:=True
Selection.Copy

More option of what you can select from a PivotTable is here and PivotTable.PivotSelect Method in this and show you how to select some specific data from a field.

Copy and paste data from columns A to AI up to the last row in pivot table

1 Answers1