Copy data from a Pivot table in one worksheet ot another

Question

I have two workbooks; in one I have a Pivot table; in the other, an empty worksheet.

I am trying to copy certain data from the pivot table to the other workbook, but it seems like VBA isnt copying my selection.

Currently I have written:

With cevesa.Worksheets("r_hora")

    On Error Resume Next
    .PivotTables("r_hora").PivotFields("PARAM").ClearAllFilters
    .PivotTables("r_hora").PivotSelection = "PARAM['DEM(MW)':'DEM_AGREGADA(MW)','POT(MW)':'POT_HID(MW)','POTDESP(MW)']"
    Selection.Copy

    analisis.Worksheets("DATOS Generación").Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    On error goto 0

End with

However, this wont work unless I activate manually the workbook with the Pivot table. It seems to me the selection.copy line is working only for the active workbook, and not pointing at the current Pivotselection.

Thanks!

you can cevesa.Activate and cevesa.Worksheets("r_hora").Activate — Slai

Shai Rado Shai Rado · Accepted Answer · 2017-08-11T12:40:13

Try the code below, explanations inside the code's comments :

Option Explicit

Sub CopyPivotItemsDataRange()

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Dim PvtRng As Range

' set the Pivot-Table object
Set PvtTbl = cevesa.Worksheets("r_hora").PivotTables("r_hora")

' set the Pivot-Table Field object
Set PvtFld = PvtTbl.PivotFields("PARAM")

With PvtFld
    .ClearAllFilters
    ' use Union to merge multipe Pivot Items DataRange
    Set PvtRng = Union(.PivotItems("DEM(MW)").DataRange, .PivotItems("DEM_AGREGADA(MW)").DataRange, _
                .PivotItems("POT(MW)").DataRange, .PivotItems("POT_HID(MW)").DataRange)
End With

PvtRng.Copy

' I let you finish the Paste section

End Sub

Copy data from a Pivot table in one worksheet ot another

1 Answers