Copy/Paste VBA Error

[tj66]

Hello,

I'm getting a compile error: Wrong number of arguments or invalid property assignment. The portion of code highlighted after I click "Ok" is in large, red text below.

Sub ListingData()

Dim i, x, numRow, numColumn, numValue As Integer
Dim rowCount As Long

Dim ws1, ws2 As Worksheet
Set ws1 = Sheets("Req")
Set ws2 = Sheets("Work")

Dim myTable As ListObject
Set myTable = ActiveSheet.ListObjects("Table3")

x = 2
rowCount = myTable.DataBodyRange.Rows.count + 5

For i = 5 To rowCount

    If ActiveSheet.Cells(i, 7).Value = "N" Then

        ws1.Range(ws1.Cells(i, 2), ws1.Cells(i, 3), ws1.Cells(i, 4), ws1.Cells(i, 5), ws1.Cells(i, 6), ws1.Cells(i, 7)).Copy
        ws2.Paste Destination:=ws2.Range(ws2.Cells(x, 1), ws2.Cells(x, 2), ws2.Cells(x, 3), ws2.Cells(x, 4), ws2.Cells(x, 5), ws2.Cells(x, 6), ws2.Cells(x, 7))
        x = x + 1
    End If

Next i
End Sub

This is the portion of code that is apparently having the issue (in red and bold text):

ws2.Paste Destination:=ws2.Range(ws2.Cells(x, 1), ws2.Cells(x, 2), ws2.Cells(x, 3), ws2.Cells(x, 4), ws2.Cells(x, 5), ws2.Cells(x, 6), ws2.Cells(x, 7))

Thank you for your help!

 

[tj66]

Hello,

I'm getting a compile error: Wrong number of arguments or invalid property assignment. The portion of code highlighted after I click "Ok" is in large, red text below.

Sub ListingData()

Dim i, x, numRow, numColumn, numValue As Integer
Dim rowCount As Long

Dim ws1, ws2 As Worksheet
Set ws1 = Sheets("Req")
Set ws2 = Sheets("Work")

Dim myTable As ListObject
Set myTable = ActiveSheet.ListObjects("Table3")

x = 2
rowCount = myTable.DataBodyRange.Rows.count + 5

For i = 5 To rowCount

    If ActiveSheet.Cells(i, 7).Value = "N" Then

        ws1.Range(ws1.Cells(i, 2), ws1.Cells(i, 3), ws1.Cells(i, 4), ws1.Cells(i, 5), ws1.Cells(i, 6), ws1.Cells(i, 7)).Copy
        ws2.Paste Destination:=ws2.Range(ws2.Cells(x, 1), ws2.Cells(x, 2), ws2.Cells(x, 3), ws2.Cells(x, 4), ws2.Cells(x, 5), ws2.Cells(x, 6))
        x = x + 1
    End If

Next i
End Sub

This is the portion of code that is apparently having the issue (in red and bold text):

ws2.Paste Destination:=ws2.Range(ws2.Cells(x, 1), ws2.Cells(x, 2), ws2.Cells(x, 3), ws2.Cells(x, 4), ws2.Cells(x, 5), ws2.Cells(x, 6), ws2.Cells(x, 7))

Thank you for your help!

 

[tj66]

I added one-too-many arguments, but that wasn't the issue. I fixed the # of arguments in the second post.

 

[Fluff]

How about

    If ws1.Cells(i, 7).Value = "N" Then

        ws1.Range("B" & i).Resize(, 6).Copy ws2.Range("A" & x)
        x = x + 1
    End If

 

[tj66]

How about

    If ws1.Cells(i, 7).Value = "N" Then

        ws1.Range("B" & i).Resize(, 6).Copy ws2.Range("A" & x)
        x = x + 1
    End If

Brilliant, thank you. I wish I understood why my .Range wasn't working, though. Your solution is definitely more elegant.

A follow-up question, if you don't mind: ws1 has a formula in the fifth column that is transferred. Can I change it so that the .Copy function takes the values rather than the formulas?

Is there some sort of kudos I can give to improve your street cred?

 

[Fluff]

To keep values only use

        ws2.Range("A" & x).Resize(, 6).Value = ws1.Range("B" & i).Resize(, 6).Value

 

[tj66]

???

(These questions are the result of self-teaching functions and not necessarily why functions work. ? )

 

[Fluff]

You're welcome & thanks for the feedback