VBA code to copy entire row IF a specific word is in a SPECIFIC column

Greetings

I am trying to work out a formula that states
if column J contains "NO" then copy that row and paste in a second sheet (which has already data on it).

A friend helped me on that and created the below formula:

[VBA]Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "NO" Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select
Selection.Copy

Sheets("Sheet2").Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
Next
End Sub[/VBA]

While it relatively works, the problem is that it copies the entire row in sheet's 2 Row 1 (because the initial data were in sheet's 1 row 1). But in sheet 2 i have other rows covered with other data and i do not want to replace them. I just want the vba code to copy the line IN THE FIRST NEW ROW that HAS NO DATA in it (e.g let's say row 15 but that is constantly changing).

How could i do that? Can someone help as i am vba noob?

P.S i "guess" it is smth with ActiveSheet.Rows(matchRow).Select line, but i m not 100% sure

Thanks

When i run it i get an error:

Run time error 91: Object variable or With Block variable not set. I see the vba stops in that line:

rng = s1.Range("J1:J" & lr)

What do you mean by that?

Ok let me be more clear. Here is my xlsx file as a guide: https://www1.zippyshare.com/v/QZY8hQmF/file.html

What i want: In sheet1 when i insert NO in K column (from drop down menu) to have this exactly row copied in the end of sheet2 (now row 19 in my xlsx). In fact i do not want the entire row but only the first 8 cells of that row.

I do not know if this is possible that's why i have post it here.

Yeap i have it in my previous post, along with what exactly i want, sorry.

I will test tomorrow and report back. Thanks!

Thanks both. I will report back tomorrow!

Hello again

[USER="31712"]Carim[/USER] i tested your file (Test Haris.xlsm). There is no vba macro in it.

[USER="108665"]AlanSidman[/USER] i tested your command and while it says "Action Complete" it does not do anything

Οh it works..but..I want the exact opposite

I have to change the " NAI " to " ΌΧΙ " . How can i edit the event macro?

Will try it thanks. My Sheet1, Sheet2 and Sheet 3 are in GREEKS, so i will try to alter your code to my needs and report back.

PS1: I tried it. Νope. Does NOT work with any other language than English. So i will have to leave my 3 tabs as Sheet1, 2 and 3.

Well this is my TRUE xlsx file and not my previous one (which was rather a trimmed one).

https://www37.zippyshare.com/v/p03IZp0C/file.html

I m trying to copy paste your code but does not work. Can you add your code (as long with PS2) to this file??

PS1 The Test Haris.xlsm works as expected !! (dunno why). Greek explanation 4 my file [NAI = yes and OXI = no]

PS2 Can you add an option when i delete column K2 (in sheet1) the entire new row (in sheet 2) would also automatically be deleted ??