How to make my Macro VBA code faster?

[SaraB802]

Hi, I am relatively new to writing in VBA in Excel and am so far self taught with the help of forums and google. Below is the code I have to run, which currently is taking upto 5mins because of the main file which is 17MB, and all the child files are 3MB. I need to run the same routine for a total of 6 different workbooks, drawing from them and copying the values in to the master workbook.

Sub Update()
'
' Update Macro
'
Workbooks.Open Filename:= _
"(location and filename)"
Windows("file name").Activate
Sheets("Ref").Select
Range("A1:F2210").Select
Selection.Copy
Windows("file name 2").Activate
Sheets("Ref").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("file name 2").Close SaveChanges:=False
Sheets("File name").Select
Range("A1").Select

End Sub

I know it is long and clunky but it has worked, all be it very slowly. Any suggestions how to make this faster would be much apperciated

 

[Jubjab]

You say you do it for 6 workbooks, but I can't see a loop in your code. Or do you run it 6 times, each time rewriting what file it is supposed to open?

 

[Jon von der Heyden]

Welcome to the board!

Tips:
1) Turn off screen updating so that the screen won't be printed while the code runs
2) Switch to manual calculation
3) Avoid selecting and activating. You can still copy a range from a non-active workbook; you just have to qualify the range.

Try this:

Public Sub Update()
    Dim lngCalc As XlCalculation
    Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook
    
    With Application
        .ScreenUpdating = False
        lngCalc = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .EnableCancelKey = xlErrorHandler
    End With

    On Error GoTo proc_End

    Set wkb1 = Workbooks.Open(Filename:="location and filename")
    Set wkb2 = ThisWorkbook '< assuming that "file name 2" is in fact the same workbook

    Call wkb1.Sheets("Ref").Range("A1:F2210").Copy
    Call wkb2.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)

    Application.CutCopyMode = False

proc_End:
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
        .EnableCancelKey = xlInterrupt
    End With
End Sub

 

[SaraB802]

Welcome to the board!

Try this:

Public Sub Update()
    Dim lngCalc As XlCalculation
    Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook
    
    With Application
        .ScreenUpdating = False
        lngCalc = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .EnableCancelKey = xlErrorHandler
    End With

    On Error GoTo proc_End

    Set wkb1 = Workbooks.Open(Filename:="location and filename")
    Set wkb2 = ThisWorkbook '< assuming that "file name 2" is in fact the same workbook

    Call wkb1.Sheets("Ref").Range("A1:F2210").Copy
    Call wkb2.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)

    Application.CutCopyMode = False

proc_End:
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
        .EnableCancelKey = xlInterrupt
    End With
End Sub

Jon - Thanks for this. If I was to add more workbooks, I am assuming I could just add them all so it would read Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook, wkb3 As excel.workbook, wkb4 As excel.workbook

Is it also possible to reverse this so I can paste from my master to the other workbooks, without activating them? Logically to me I could just swap the references around as needed.