CODEWITHSUNDEEP
vbaexcel
Intern87
Intern87

Reputation: 479

copy paste Range in VBA Excel

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

Upvotes: 2

Views: 48572

Answers (1)

whytheq
whytheq

Reputation: 35557

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

Upvotes: 7

Related Questions