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
– Vasim, Commented Aug 29, 2013 at 7:38
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
– shahkalpesh, Commented Aug 29, 2013 at 7:40

whytheq · Accepted Answer · 2013-08-29 07:40:14Z

7

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

answered Aug 29, 2013 at 7:40

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whytheq Over a year ago

(@Vasim sorry for coping and pasting your comment! ... let me see if I can make it up to you...) ; now you've jumped to 300 points!!!

Collectives™ on Stack Overflow

copy paste Range in VBA Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related