CODEWITHSUNDEEP
vbaexcelcopy-paste
Yasmine Nouri
Yasmine Nouri

Reputation: 103

Copy results from one workbook to another

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

Upvotes: 0

Views: 159

Answers (1)

simpLE MAn
simpLE MAn

Reputation: 1622

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)

Upvotes: 1

Related Questions