Copy data from different workbooks and refresh pivot table EXCEL VBA

Question

i have 2 CSV files orders_by_user_backlog and orders_by_user_pivot. I want to create with VBA an .xlsx file called Orders by Users Report containing 2 sheets.

First sheet called backlog, i will just copy the data from orders_by_user_backlog.csv and paste it there.

Second sheet called pivot, i will copy the data from orders_by_user_pivot.csv and paste it there and refresh the pivot table in the same sheet with the new data.

I am using an .xlsx file as template called Orders By Users Template

Here is my code

Sub Orders_by_User_report()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False 

    'open the csv output from query orders by user backlog
    Workbooks.Open FileName:="C:\Reports\orders_by_user_backlog.csv"
    Cells.Select
    Selection.Copy
    Workbooks.Open FileName:= _
        "C:\Reports\Report templates\Orders By Users Template.xlsx"
    Sheets(1).Select
    Cells.Select
    ActiveSheet.Paste

    'Bold and color top row, add autofilter, zoom 80% and autofit        
    Range("A1:H1").Select
    Selection.Interior.Color = 255
    Selection.Font.Bold = True
    Selection.AutoFilter
    ActiveWindow.Zoom = 80
    Cells.Select
    Cells.EntireColumn.AutoFit

     'open the csv output from query orders by user pivot
    Workbooks.Open FileName:="C:\Reports\orders_by_user_pivot.csv"
    Cells.Select
    Selection.Copy

    Workbooks.Open FileName:= _
        "C:\Reports\Report templates\Orders By Users Template.xlsx"
    Sheets(2).Select
    Cells.Select
    ActiveSheet.Paste

    'Bold and color top row, add autofilter, zoom 80% and autofit

    Range("A1:D1").Select
    Selection.Interior.Color = 255
    Selection.Font.Bold = True
    Selection.AutoFilter
    ActiveWindow.Zoom = 80
    Cells.Select
    Cells.EntireColumn.AutoFit

   'refresh pivot table 

    Sheets(2).Select
   ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh


    'save file as xlsx for the output
    ActiveWorkbook.SaveAs FileName:= _
        "C:\Reports\Orders by Users\Orders by Users Report " & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWindow.Close
    ActiveWindow.Close

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

I know where is my problem, it's in the 'ActiveSheet' but i didn't know how to fix it. Everytime i am using

ActiveSheet.Paste

or

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

it shows me error in the second ActiveSheet.Paste, when i comment this part and try only with one CSV file it Pastes fine but then it stops again in the ActiveSheet.PivotTables.

Can anyone please advice me what to change in my code ? Thank you very much.

I tried it before and it wasn't working. The problem is in the ActiveSheet because it stops running there from the second ActiveSheet.Paste — JuniorDev
Don't use the activesheet method, it's not great. Define your sheets explicitly ie Worksheet("Sheet1").PivotTables("PivotTable1") — Preston
@JuniorDev if you are updating your PivotTable's Data Range, then you need to update your PivotCache first, and then update your PivotTable. — Shai Rado

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-11-03T13:18:25

Assign parentage to your objects and work directly with the objects. It's cleaner, easier to read and debug and ensures you are working directly with the objects you want.

Code below is untested (and only deals with the question at hand).

Dim wbBacklog as Workbook, wbPivot as Workbook, wbTemplate as Workbook

Set wbTemplate = Workbooks.Open("C:\Reports\Report templates\Orders By Users Template.xlsx")
Set wbBacklog = Workbooks.Open("C:\Reports\orders_by_user_backlog.csv")
Set wbPivot = Workbooks.Open("C:\Reports\orders_by_user_backlog.csv")

wbBacklog.Sheets(1).UsedRange.Copy 'copy data
wbTemplate.Sheets(1).Range("A1").PasteSpecial xlPasteValues 'paste to template

wbPivot.Sheets(1).UsedRange.Copy 'copy data
wbTemplate.Sheets(2).Range("A1").PasteSpecial xlPasteValues

Also, to make it easier, define a dynamic named range for the data source of the PivotTable. SOmething like this formula.

=Offset(Sheet2!A1,0,0,counta(Sheets2!A:A),4)

Then, in your code,

wbTemplate.Sheets(2).PivotTables(1).Refresh

will work.

Lastly, read and study how and why to Avoid Select. It's a must in VBA programming in Excel.

Copy data from different workbooks and refresh pivot table EXCEL VBA

1 Answers