title: Excel VBA taking long time to copy date from one workbook to another
body: <p>I am trying to copy data from one excel to another using VBA. But its taking more than 15 mins for 30K lines. Is there a way I could make it faster?</p>
  
  <p>I need to align 39 columns from New Report workbook to ACQ047 WB.</p>
  
  <p>Below is my code:</p>
  
  <pre><code>Sub alignment()
  
  Dim x As Workbook
  Dim y As Workbook
  
  Set x = Workbooks.Open("C:\Users\raja\Desktop\New Report.xls")
  Set y = Workbooks.Open("C:\Users\raja\Desktop\ACQ047.xlsx")
  
  Dim Lastrow As Long
  
  
  y.Sheets("unmached").Range("A2").Activate
  y.Sheets("unmached").Rows(ActiveCell.Row &amp; ":" &amp; Rows.Count).Delete Shift:=xlUp
  
  
  
  
  x.Sheets("New Report").Rows(1).EntireRow.Delete
  x.Sheets("New Report").Range("A1").EntireRow.Insert
  Lastrow = x.Sheets("New Report").Range("A" &amp; Rows.Count).End(xlUp).Row
  
  Application.ScreenUpdating = False '!!!!
  Application.Calculation = xlCalculationManual '!!!!
  
  For i = 1 To Lastrow
  CopyVal = x.Sheets("New Report").Range("A1").Offset(i, 2).Value
  CopyVal2 = x.Sheets("New Report").Range("A1").Offset(i, 6).Value
  CopyVal3 = x.Sheets("New Report").Range("A1").Offset(i, 8).Value
  CopyVal4 = x.Sheets("New Report").Range("A1").Offset(i, 11).Value
  CopyVal5 = x.Sheets("New Report").Range("A1").Offset(i, 12).Value
  CopyVal6 = x.Sheets("New Report").Range("A1").Offset(i, 14).Value
  CopyVal7 = x.Sheets("New Report").Range("A1").Offset(i, 16).Value
  CopyVal8 = x.Sheets("New Report").Range("A1").Offset(i, 18).Value
  CopyVal9 = x.Sheets("New Report").Range("A1").Offset(i, 19).Value
  CopyVal10 = x.Sheets("New Report").Range("A1").Offset(i, 20).Value
  CopyVal11 = x.Sheets("New Report").Range("A1").Offset(i, 21).Value
  CopyVal12 = x.Sheets("New Report").Range("A1").Offset(i, 22).Value
  CopyVal13 = x.Sheets("New Report").Range("A1").Offset(i, 23).Value
  CopyVal14 = x.Sheets("New Report").Range("A1").Offset(i, 25).Value
  CopyVal15 = x.Sheets("New Report").Range("A1").Offset(i, 26).Value
  CopyVal16 = x.Sheets("New Report").Range("A1").Offset(i, 28).Value
  CopyVal17 = x.Sheets("New Report").Range("A1").Offset(i, 30).Value
  CopyVal18 = x.Sheets("New Report").Range("A1").Offset(i, 32).Value
  CopyVal19 = x.Sheets("New Report").Range("A1").Offset(i, 33).Value
  CopyVal20 = x.Sheets("New Report").Range("A1").Offset(i, 35).Value
  CopyVal21 = x.Sheets("New Report").Range("A1").Offset(i, 40).Value
  CopyVal22 = x.Sheets("New Report").Range("A1").Offset(i, 41).Value
  CopyVal23 = x.Sheets("New Report").Range("A1").Offset(i, 49).Value
  CopyVal24 = x.Sheets("New Report").Range("A1").Offset(i, 50).Value
  CopyVal25 = x.Sheets("New Report").Range("A1").Offset(i, 46).Value
  CopyVal26 = x.Sheets("New Report").Range("A1").Offset(i, 48).Value
  CopyVal27 = x.Sheets("New Report").Range("A1").Offset(i, 43).Value
  CopyVal28 = x.Sheets("New Report").Range("A1").Offset(i, 29).Value
  CopyVal29 = x.Sheets("New Report").Range("A1").Offset(i, 53).Value
  CopyVal30 = x.Sheets("New Report").Range("A1").Offset(i, 54).Value
  CopyVal31 = x.Sheets("New Report").Range("A1").Offset(i, 55).Value
  CopyVal32 = x.Sheets("New Report").Range("A1").Offset(i, 56).Value
  CopyVal33 = x.Sheets("New Report").Range("A1").Offset(i, 57).Value
  CopyVal34 = x.Sheets("New Report").Range("A1").Offset(i, 59).Value
  CopyVal35 = x.Sheets("New Report").Range("A1").Offset(i, 60).Value
  CopyVal36 = x.Sheets("New Report").Range("A1").Offset(i, 61).Value
  CopyVal37 = x.Sheets("New Report").Range("A1").Offset(i, 62).Value
  CopyVal38 = x.Sheets("New Report").Range("A1").Offset(i, 63).Value
  CopyVal39 = x.Sheets("New Report").Range("A1").Offset(i, 64).Value
  
  
  
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 38).Value = CopyVal39
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 37).Value = CopyVal38
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 36).Value = CopyVal37
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 35).Value = CopyVal36
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 34).Value = CopyVal35
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 33).Value = CopyVal34
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 32).Value = CopyVal33
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 31).Value = CopyVal32
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 30).Value = CopyVal31
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 29).Value = CopyVal30
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 28).Value = CopyVal29
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 27).Value = CopyVal28
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 26).Value = CopyVal27
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 25).Value = CopyVal26
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 24).Value = CopyVal25
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 23).Value = CopyVal24
    y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 22).Value = CopyVal23
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 21).Value = CopyVal22
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 20).Value = CopyVal21
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 19).Value = CopyVal20
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 18).Value = CopyVal19
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 17).Value = CopyVal18
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 16).Value = CopyVal17
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 15).Value = CopyVal16
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 14).Value = CopyVal15
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 13).Value = CopyVal14
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 12).Value = CopyVal13
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 11).Value = CopyVal12
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 10).Value = CopyVal11
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 9).Value = CopyVal10
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 8).Value = CopyVal9
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 7).Value = CopyVal8
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 6).Value = CopyVal7
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 5).Value = CopyVal6
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 4).Value = CopyVal5
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 3).Value = CopyVal4
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 2).Value = CopyVal3
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 1).Value = CopyVal2
  y.Sheets("Unmached").Range("A1048576").End(xlUp).Offset(1, 0).Value = CopyVal
  
  Next
  
  
  Application.Calculation = xlCalculationAutomatic '!!!!
  Application.ScreenUpdating = True '!!!
  
  y.Sheets("unmached").Range("A1").Select
  
  ActiveWorkbook.Close SaveChanges:=True
  
  x.Sheets("New Report").Range("A1").Select
  
  ActiveWorkbook.Close SaveChanges:=False
  
  MsgBox " Report Generated"
  
  
  
  End Sub
  </code></pre>