用于将条件数据复制到特定单元格的 VBA 宏

我是 VBA 编程新手，我正在寻找从匹配条件的不同工作表中获取数据。然后从一个特定单元格复制并粘贴到另一个特定单元格 7 次。我的代码不起作用，我正在寻求改进它。当我运行代码时，我在 IF 语句开头被标记为运行时错误“1004”方法“对象范围”_Worksheet”失败。

 Sub CopyValues()

 'Declare variables
 'Declare sheet variables
 Dim Sourcews As Worksheet
 Dim Pastews As Worksheet

 'Declare counter variables
 Dim i As Integer
 Dim n As Integer
 Dim lastrow As Long

 Set Sourcews = ThisWorkbook.Sheets("Source")
 Set Pastews = ThisWorkbook.Sheets("Paste")

  lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For i = 3 To lastrow

 If Sourcews.Range(i, "AA").Value = "Needed Value" Then

    Pastews.Cells("C:18").Paste
    Pastews.Cells("D:18").Paste
    Pastews.Cells("E:18").Paste
    Pastews.Cells("F:18").Paste
    Pastews.Cells("G:18").Paste
    Pastews.Cells("H:18").Paste



End If

Next

尝试这个。我假设您想要粘贴到第 18 行，然后粘贴到第 19 行等，而不是重复粘贴到第 18 行！

Sub CopyValues()

'Declare variables
'Declare sheet variables
Dim Sourcews As Worksheet
Dim Pastews As Worksheet

'Declare counter variables
Dim i As Long
Dim n As Long
Dim lastrow As Long

Set Sourcews = ThisWorkbook.Sheets("Source")
Set Pastews = ThisWorkbook.Sheets("Paste")

lastrow = Sourcews.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
n = 18

For i = 3 To lastrow
    If Sourcews.Cells(i, "AA").Value = "Needed Value" Then
        Sourcews.Cells(i, "AA").Copy Pastews.Cells(n, "C").Resize(, 6)
        n = n + 1
    End If
Next

End Sub