VBA to automatically copy row between sheets based on cell value

  • Hi Excel gurus

    I am looking for a code that will automatically copy an entire row from sheet 1 to sheet 2 (next available row) once the value 'Yes' in column H is selected from a drop down list

    The workbook will be in constant use for many years to come, and will need to keep previous historical entries (no overwriting)

    Sheet 2 will eventually be used to populate a third sheet where the values are laid in a report format but I only need the the auto population of sheet 2 at this stage

    I'm pretty new to VBA and all efforts to use BBA code for similar issues have ended in failure so any guidance appreciated


    I've attached a copy of the workbook if it helps

  • Hello and Welcome to the Forum :)


    Since there are two ' lost ' columns ( P and Q ) ... should they be excluded from the copy process ... or not ...?

    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...:)

  • Hey Carim


    Thanks for the welcome and the prompt response - works perfectly

    The 'lost' columns are to convert % to cubic metres (the source data only displays the % value)


    If I was going to insert the two lost columns (and formula) between columns J/K and L/M respectively, would I have to adjust the code?


    Cheers again for your assistance - it's awesome


  • Thanks for your Thanks AND for the Like :)

    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...:)

  • It's much better to keep all data on one sheet, then build reports from that data

    I agree but the workbook has 2 purposes for 2 different groups

    1. to capture all data on sheet 1 and

    2. separate all data that meets the 'yes' criteria and build a report based on that data only


    Unfortunately the report has a specific layout and text that must be followed.

    For this little amateur, it's easier format the report and cross reference cells as appropriate. That said, when I get to that stage I may come back and pick your brains for a smarter way


    Cheers

  • Hi again


    Quote

    If I was going to insert the two lost columns (and formula) between columns J/K and L/M respectively, would I have to adjust the code?

    Yes ... you would need to replace the letter M by the letter Q


    Sheet2.Range("A" & last & ":" & "Q" & last).Value = Sheet1.Range("A" & Target.Row & ":" & "Q" & Target.Row).Value

    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...:)

  • You only need to set up a report page and then use AdvancedFilter and a little VBA to run the Filter. If you only have the Yes Criteria you can use AutoFilter and automate it with vba to copy the relevant data. A button to run the code will allow users to create the report when needed.

  • Thanks again Carim,

    I went through the code and worked it out - very nice code, a lot prettier than anything I'd come up with.

    Glad to hear everything is sorted out ...:)

    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...:)