vba - 复制和粘贴多个单元格错误 450

Question

我正在尝试根据某些条件将多个单元格复制到新工作表中。

但我收到一条错误消息:

"Wrong no. of arguments or invalid property assignment"

Sub sort()

Set ws = ThisWorkbook.Sheets(Array("3rd and 4th Floor Columns", "3rd Floor Beams", "Test1", "Test2"))


For k = 12 To 360
With ws(1)
i = 1
j = 1
 Select Case ws(1).Cells(k, 9).Value

 Case 26

 ws(1).Range(.Cells(k, 9), .Cells(k, 12)).Copy
 ws(3).Range(.Cells(i, 1), .Cells(i, 4)).PasteSpecial xlPasteValues

 Application.CutCopyMode = False
 i = i + 1

 Case 57

 ws(1).Range(.Cells(k, 9), .Cells(k, 12)).Copy
 ws(4).Range(.Cells(j, 1), .Cells(j, 4)).PasteSpecial xlPasteValues 'Application defined.. error

 Application.CutCopyMode = False
 j = j + 1

Case Else

End Select
End With
Next k
End Sub

Answer 1

您只需输入左上单元格和 右下单元格至定义一个范围 ，因此您的代码将如下所示:

For k = 12 To 360
With ws(1)
    i = 1
    j = 1
    Select Case .Cells(k, 9).Value
        Case 26
            ws(1).Range(.Cells(k, 9), .Cells(k, 12)).Copy
            ws(3).Range(ws(3).Cells(i, 1)).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            i = i + 1
        Case 57
            ws(1).Range(.Cells(k, 9), .Cells(k, 12)).Copy
            ws(4).Range(ws(4).Cells(j, 1)).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            j = j + 1
        Case Else
    End Select
End With