I am trying to accelerate my Excel VB Macro significantly. I have tried the 5 alternatives below. I wonder if I could shorten the execution time further.


Sub AccelerateMacro()
'
' v1 052817 by eb+mb
' Macro to copy as fast as possible sheet from one workbook into another workbooks
' Declarations for variables are not shown to make code example more legible


' Macro is stored in and run from "DestinationWorkBook.xlsm"
StartTime = Timer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Alternative = "First"
If Alternative = "First" Then
Workbooks.Open Filename:="SourceWorkBook.xls"
Cells.Select
Selection.Copy
Windows("DestinationWorkBook.xlsm").Activate
Sheets("DestinationSheet").Select
Range("A1").Select
ActiveSheet.Paste
Windows("SourceWorkBook.xls").Activate
ActiveWorkbook.Close
End If
If Alternative = "Second" Then
Workbooks.Open Filename:="SourceWorkBook.xls", ReadOnly:=True
Cells.Select
Selection.Copy
Windows("DestinationWorkBook.xlsm").Activate
Sheets("DestinationSheet").Select
Range("A1").Select
ActiveSheet.Paste
Workbooks("SourceWorkBook.xls").Close SaveChanges:=False
End If
If Alternative = "Third" Then
' I could not get this alternative to work
Workbooks.Open("SourceWorkBook.xls").Worksheets("S ourceSheet").Copy
Workbooks.Open("DestinationWorkBook.xlsm").Workshe ets("DestinationSheet").Range("A1").PasteSpecial
End If
If Alternative = "Fourth" Then
' I could not get this alternative to work
Workbooks.Open("DestinationWorkBook.xlsm").Workshe ets("DestinationSheet").Range("A1") = Workbooks.Open("SourceWorkBook.xls").Worksheets("S ourceSheet")
End If
If Alternative = "Fifth" Then
' I don't understand the code in this alternative
Dim wbIn As Workbook
Dim wbOut As Workbook
Dim rSource As Range
Dim rDest As Range
Set wbOut = Application.Workbooks.Open("DestinationWorkBook.xl sm")
Set wbIn = Application.Workbooks.Open("SourceWorkBook.xls")
With wbIn.Sheets("SourceSheet").UsedRange
wbOut.Sheets("DestinationSheet").Range("A1").Resiz e(.Rows.Count, .Columns.Count) = .Value
End With

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

I didn't try to read the whole program to see which techniques you used. But here's a quick list of the things I do to speed up an Excel VBA program:

1) Turn off screen updating (Application.ScreenUpdating); I see you're doing that already.

2) It often helps to turn off automatic calculation (Application.Calculation=xlCalculationManual).

Don't forget to restore these at the end of your program, or if you're abending. I keep a class around to handle both of these settings and a few more; it automatically detects and saves the status of each setting when it's created, and includes a method I can call at the end of the program or during Abend to restore them all before quitting.

3) If you're reading or writing large amounts of data, say more than 100 cells, this one makes a huge difference: Whenever possible, read a range into a two-dimensional array (it has to be two dimensions even if you're looking only at a single column or row), process it there, and write the array back to a range. This is more work at the programming end, but it can turn a three-minute program into a half-second program—well worth the trouble if you're going to use it more than once, and after you get used to using arrays you'll have the techniques down pat and won't think it much of an extra burden.

4) I have the feeling I'm missing one, but I can't remember...