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 .Columns(c.Column).Copy r.PasteSpecial xlPasteValues End If Next Application.CutCopyMode = False End With Set fileDialog = Nothing Set wbSource = Nothing End Sub