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
