VBA to automatically copy row between sheets based on cell value

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Quote from royUK

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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.

    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.

    Quote from H123

    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 "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login