[SOLVED] Copy each row from specific columns to another worksheet if criteria is met

Sub Services()

Dim i, x As Long
Dim airport As String
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Raw Data")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Services")

x = 11
airport = "BM"

For i = 2 To 500:
If ws1.Cells(i, 9) = airport Then
Range(ws2.Cells(x, 1), ws2.Cells(x, 2), ws2.Cells(x, 3), ws2.Cells(x, 4), ws2.Cells(x, 5)).Value = 'This is where it errors' -->Range(ws1.Cells(i, 1), ws1.Cells(i, 2), ws1.Cells(i, 4), ws1.Cells(i, 7), ws1.Cells(i, 8)).Value
x = x + 1
End If
Next i

End Sub



The error: It highlights "Range" and comes up with a message saying "wrong number of arguments or invalid property assignment". NEED HELP ASAP PLEASE. Thanks in advance!

I think this is a prime example for the use of autofilter. This is untested since you didn't submit a workbook. You may need to amend to make it work on your specific sheet/layout but the code itself is solid.

Please Login or Register  to view this content.

Try this…

It looks longer but makes it easy to see the corresponding cells that change values (easier to debug and change too).
There are several ways to make it more efficient too.

Please Login or Register  to view this content.