Copy results from one workbook to another

Question

My code takes a while to run. I would like to copy results from one sheet (wbkorigin) to several sheets (many wbkdestination). I've tried it for one sheet to another ( 1 to 1) and it really takes a while to run. Can you help me optimize my code? thanks!!

Option Explicit
Sub update()
    Dim wkbkorigin As Workbook
    Dim wkbkdestination As Workbook
    Dim originsheet As Worksheet
    Dim destsheet As Worksheet

    Set wkbkorigin = ActiveWorkbook

    Set wkbkdestination = Workbooks.Open("link to worksheet")

    Set originsheet = wkbkorigin.Worksheets("sheet1")
    Set destsheet = wkbkdestination.Worksheets("Sheet1")
    originsheet.Range("D4:Q5").Copy
    destsheet.Range("A1").PasteSpecial
    wkbkdestination.Close SaveChanges:=True

End Sub

The code you have is exactly what I'd use as well, except for one thing: `Application.ScreenUpdating = False` after your declaration area should work wonders. See my answer [here](http://stackoverflow.com/questions/20738373/can-i-make-this-macro-more-efficient-or-faster/20754562#20754562) for an explanation, #5 to be exact.. :) — WGS, Feb 03 '14 at 16:04

score 1 · Accepted Answer · answered Feb 03 '14 at 16:09

Maybe this will accelerate a bit:

Sub update()

    'Put calculation to xlCalculationManual and screenupdating to Off
    Dim calcState As XlCalculation
    calcState = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim wkbkorigin As Workbook
    Dim wkbkdestination As Workbook
    Dim originsheet As Worksheet
    Dim destsheet As Worksheet

    Set wkbkorigin = ActiveWorkbook

    Set wkbkdestination = Workbooks.Open("link to worksheet")

    Set originsheet = wkbkorigin.Worksheets("sheet1")
    Set destsheet = wkbkdestination.Worksheets("Sheet1")

    'Copy/Paste in one step
    originsheet.Range("D4:Q5").Copy destsheet.Range("A1")

    Application.Calculate
    Application.ScreenUpdating = True
    Application.Calculation = calcState

    wkbkdestination.Close SaveChanges:=True

End Sub

1) Put calculation to manual;
2) ScreenUpdating to off;
3) Copy/Paste in one step since you don't use the pastespecial options (I don't think this line will speed up the process but it is cleaner)

Is it possible to do the same without opening the file (wrkbkdestination) : it could also save some time... ? — Yasmine Nouri, Feb 03 '14 at 16:24
@YasmineNouri; No, it is not possible to alter workbook data without opening it. — simpLE MAn, Feb 03 '14 at 16:27
too bad... So if i have to export results from one workbook to several workbooks, it is going to open every workbook... It will take a lot of time. — Yasmine Nouri, Feb 03 '14 at 16:30

Copy results from one workbook to another

1 Answers1