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

  • Try this code instead:


  • Hello,


    In order to avoid all possible misunderstandings ... you should attach a sample file with your next message ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    Attached is your test file ... :wink:


    ελπίζω ότι αυτό θα βοηθήσει

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)


  • 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.


    [USER="108665"]AlanSidman[/USER]



    Hello Alan,


    It seems Haris is looking for an Event macro to copy the Target Row to Sheet2 .. as he inputs NO in Column K ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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

  • Hello again


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



    Hello,


    There is NO standard macro ... But an Event Macro ...:wink:


    Just input NAI in cell K2 ... and Go see the result in Sheet 2 ... :smile:


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Glad you could test the Event Macro ...


    Below is the OXI correction :


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 ??