复制粘贴范围在VBA Excel [英] copy paste Range in VBA Excel

问题描述

我正在尝试将一行值从一张表复制到另一张，但仍然出现运行时错误1004：应用程序定义或对象定义的错误。
错误在下面的两行的第一行，我不知道我在哪里错了。

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

推荐答案

正如Vasim的评论一样 - 在 CA ： c>

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

这篇关于复制粘贴范围在VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！