I have this code below that copies data into a master workbook, in sheets "Inputs1", by matching the column headers from the master workbook with the source workbook. If a column header matches, it will copy and paste that column to the related column in the master workbook.

The macro works great for smaller files, but once I run it on larger files it takes almost 2 minutes to run. I also noticed it runs slower when called from a userform instead of from the developer tab directly.

How can I improve this code to run more efficiently and quickly? I still get page flickers too.

Sub Import()

 Application.ScreenUpdating = False

EventState = Application.EnableEvents
Application.EnableEvents = False

CalcState = Application.Calculation
Application.Calculation = xlCalculationManual

PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False

Dim wbSource As Workbook
Dim wbDest As Workbook
Dim TargetSheet As Worksheet
Dim c As Range
Dim rng As Range
Dim i As Integer
Dim MyRange As Range
Dim SourceSheet As Worksheet
Dim source As String
Dim dest As String
Dim r As Range
Dim msg As String

'Source and destination workbooks defined by cell value

source = Worksheets("Set-Up").Range("B11")
dest = Worksheets("Set-Up").Range("B8")

Set wbSource = Workbooks(source)
Set SourceSheet = wbSource.Worksheets("HFL01 Extract")

Set wbDest = Workbooks(dest)
Set TargetSheet = wbDest.Worksheets("INPUTS1")

With SourceSheet.Range("A1").CurrentRegion
    For Each r In TargetSheet.Range("A1:cc1")
        Set c = .Rows(1).Find(r.Value, , , xlWhole, , 0)
        If Not c Is Nothing Then
            r.PasteSpecial xlPasteValues

        End If
    Application.CutCopyMode = False
End With

Set fileDialog = Nothing
Set wbSource = Nothing

End Sub
  • \$\begingroup\$ You are copying entire columns, this will slow down your code. Try only copying the range. Test that for speed. Also test if copying your data into an array will help the speed. I regulary deal with 100,000+ lines in excel and my macros take mere seconds.. \$\endgroup\$ – alowflyingpig Oct 14 '19 at 23:06

Copying and pasting isn't necessary and interferes with the user's paste cache. Clear the target column of data and then set a range in the target the same size as the source. Then use an assignment statement:

TargetRange.Value = SourceRange.Value
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.