title: How to copy specific columns from one sheet and paste in another sheet in a different range?
body: <p>I am a beginner and I have this code below, but this just copies the last row from sheet and pastes into sheet 2 range. Basically the empty row is not getting updated. And also throws run time error 1004 - Application defined or object defined error. Any help would be much appreciated.</p>
  
  <pre><code>Sub copypaste()
  Dim lastrow As Long, erow As Long
  
  lastrow = ThisWorkbook.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To lastrow
  
  Sheet1.Cells(i, 3).Copy
  erow = ThisWorkbook.Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 2)
  Sheet1.Cells(i, 14).Copy
  Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 4)
  Sheet1.Cells(i, 6).Copy
  Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)
  
  Next i
  
  Application.CutCopyMode = False
  ThisWorkbook.Worksheets("sheet2").Columns().AutoFit
  Range("A1").Select
  
  End Sub
  </code></pre>
  
  <p>Lets say this is sheet 1
  <a href="http://i.stack.imgur.com/PZRUM.png" rel="nofollow"> sheet 1</a></p>
  
  <p>and this is sheet2:
  <a href="http://i.stack.imgur.com/kV3F4.png" rel="nofollow">sheet2:</a></p>
  
  <p>Where the headers are slightly different in naming and there's also a position change. And the last row in sheet 1 is varied.</p>