title: copy paste Range in VBA Excel
body: <p>I am trying to copy paste a row of values from one sheet to another but keep coming up with the run-time error 1004: Application-defined or object-defined error.
  the error is in the first line of the two below and I do not know where I am going wrong. </p>
  
  <pre><code>Set copyRange = Worksheets("Sheet2").range(A &amp; i + 1 &amp; CA &amp; i + 1)
  copyRange.Copy Destination:=Cells(countD, 2)
  </code></pre>
  
  <p>the code needs to copy a line at a time and paste it into the default sheet. </p>
  
  <p><strong>Edit</strong>
  full code</p>
  
  <pre><code>Dim List1 As range
  Dim List2 As range
  Dim lastRow As Integer
  Dim countD As Integer
  Dim found As Boolean
  Dim copyRange As range
  
  Set List1 = ThisWorkbook.Sheets("Sheet1").range("H2:H600")
  Set List2 = ThisWorkbook.Sheets("Sheet2").range("I2:I600")
  countD = 2
  lastRow = Application.CountA(ThisWorkbook.Sheets("Sheet2").range("C:C"))
  
  For i = lastRow To 2 Step -1
      found = False
      value1 = List1.Item(i, 1)
      For Each value2 In List2
          If value1 = value2 Then
              found = True
  
              Exit For
          End If
      Next
  
      If found = False Then
          Set copyRange = Sheets("Sheet1").range("A" &amp; i + 1 &amp; "CA" &amp; i + 1)
          copyRange.Copy Destination:=Cells(countD, 2)
          Sheets("Discrepancies").Cells(countD, 1) = "name not found"
          ThisWorkbook.Sheets("Sheet1").Cells(i + 1, 1).EntireRow.Delete
          Cells(countD, 8).Interior.ColorIndex = 3
  
          countD = countD + 1
  
      End If
  
  Next
  </code></pre>