copy paste Range in VBA Excel

Question

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.

Set copyRange = Worksheets("Sheet2").range(A & i + 1 & CA & i + 1)
copyRange.Copy Destination:=Cells(countD, 2)

the code needs to copy a line at a time and paste it into the default sheet.

Edit full 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" & i + 1 & "CA" & 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

I would suggest providing your full code...to identify the actual error.... — Vasim
What does the value A and CA contain? If that should refer to column A an column CA, try Set copyRange = Worksheets("Sheet2").range("A" & (i + 1) & ":CA" & (i + 1)). This will cover the entire area from A2 to CA2 (assuming i is 1). — shahkalpesh

whytheq whytheq · Accepted Answer · 2013-08-29T07:40:14

Exactly as Vasim's comment mentions - with the addition of a colon : in front of the CA

Sub copyRangeOver()

Dim i As Integer
i = 6

Dim copyRange  As Range
Set copyRange = ThisWorkbook.Worksheets("Sheet2").Range("A" & i + 1 & ":CA" & i + 1)

Dim countD As Integer
countD = 10
copyRange.Copy Destination:=Cells(countD, 2)


End Sub

copy paste Range in VBA Excel

1 Answers