在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.

我试图将一行值粘贴从一个工作表复制到另一个工作表,但不断出现运行时错误1004:应用程序定义或对象定义的错误。错误是在下面两个的第一行,我不知道我哪里出错了。

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

1 个解决方案

#1

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

正如Vasim的评论所提到的那样 - 加上一个冒号:在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