Move row to another sheet based on text selected in cell

  • Hi Folks


    VBA is not my strong point, but I'm trying set something up where if the word "Yes" is selected in a particular cell (col P) then, when the macro is run, it takes the contents of that row from Column A through to P and moves those contents to the next available row on another sheet.


    The caveat is that multiple rows may have Yes selected before the macro is run. In that case it needs to go through them one by one and move each of the selected rows to the other sheet.


    The code below seems to work to a point but only for one selection at a time. In addition it also overwrites what has also already been moved to the other sheet.


    Any advice or help please, I'm well and truly stuck..




    Kind regards


    DezB

  • Hello DezB,


    Automating the AutoFilter should speed things up for you:-


    I'm assuming that your input sheet has headings in row1 with data starting in row2.


    I hope that this helps.


    Cheerio,

    vcoolio.

  • Hi vcoolio


    The headings are in row 3, there's data and instruction in rows 1 and 2, and the data being moved needs to start at row 3 on the receiving sheet, sorry, I should have mentioned that.


    Let me have a go, thank you...


    Kind regards


    Dezb

  • Hello DezB,


    Replace the code that you have in the sample file with:-

    The code above should do the task for you.


    Cheerio,

    vcoolio.

  • Hi vcoolio


    That worked just fine until I aaded a new row onto the first sheet, selected Yes and ran the macro, then it overwrote the entries on the receiving sheet? Any ideas?


    Thnak you for your help, it's really appreciated.


    Kind regards


    Dezb

  • Hello DezB,


    I've attached your sample workbook with the code implemented. I've added a few rows of data to test with and you'll note that it all works just fine.


    DezB.xlsm


    Cheerio,

    vcoolio.

  • Hi Both.


    Sorry to be a pain, but could I ask, if I need to add more columns, I understand that I need to change the cell that contains "Yes" from P3 to whatever the new cell is but what I don't understand is the line of code:


    .Offset(1, -15).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)


    I'm sure that the "16" refers to the number of columns to use but could you explain to me what the -15 and the (3) (2) bit means.


    I may be coming across as bit dense and I apologise but VBA is not my strong point...


    Many thanks again.


    Kind regards


    DezB

  • In my code sample I've tried to make this manageable as follows.


    First there is this line of code:

    Code
    1. 'this is where the header row is assumed to be on both sheets
    2. Const StartRow = 3

    This points to the header row on both the Active and Archive sheets.


    Second this line of code:

    Code
    1. 'column P is assumed to contain the Yes/No values
    2. rSrc.AutoFilter Field:=.Range("P" & StartRow).Column, Criteria1:="Yes"


    So if P3 has now become say R4 you would change to:

    Code
    1. Const StartRow = 4

    and:

    Code
    1. rSrc.AutoFilter Field:=.Range("R" & StartRow).Column, Criteria1:="Yes"


    Also, the following line of code:

    Code
    1. Set rSrc = .Range("A" & StartRow).CurrentRegion

    will automatically cater for more columns (and rows).


    My aim is to make changes relatively simple to implement.

  • You are welcome, there are many ways to do the same thing in Excel.

    I was taught a long time ago to write code to make it as easy as possible to follow and maintain.


    You can take this many steps further as required eg, you could actually write code to find the column with the specific header containing the Yes/No values. This would make it easier again to add more data.

  • Thank you, er, I think I'll get to that once I've got my head around this part.. :-) The problem is that there are other cells where a Yes/No value must be selected so the way you've done is better..


    Thanks again.


    DezB

  • Hello DezB,


    In reply to your questions:-


    Code
    1. .Offset(1, -15).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    With the code in post #5, Column P(16) is the criteria column on which the filter is applied. Once applied, the filtered data to be copy/pasted starts on the next row down from the headings row, hence: .Offset(1). However, since the criteria is in Column P and Columns A:P are the required columns to be copy/pasted, we need to resize from Column A (otherwise .Offset(1) will only copy/paste the "Yes" criteria). Hence .Offset(1,-15).Resize(,16). The -15 takes us back to Column A and the resize property (,16 columns) does the rest.


    .End(3) (2) is simply the enumeration for .End(xlUp).Offset(1). They are exactly the same thing, just less typing!


    If row2 in your data set is completely empty, then the code could be written as follows:-


    You'll note the Autofilter is now set to: .AutoFilter 16 (to identify Column P within the CurrentRegion).


    The copy line of code is simply


    Code
    1. .Offset(1).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    which takes the entire (entire rows) filtered data set and copy/pastes it. If you're only interested in Columns A:P and nothing beyond, then you could use this:-


    Code
    1. .Offset(1).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    With more columns, just change the resize value. If the precise amount of columns to be copy/pasted isn't an issue then just change

    .Resize(, 16) to .EntireRow


    All in all, the CurrentRegion method is a very versatile way of doing tasks like yours so, if you add more columns, they will be instantly added to the whole procedure. You'll only need to change the reference to the criteria column.

    Take note though, when using the CurrentRegion method, if your data doesn't start in row1 with headings, then the data needs to be bounded by at least one empty row and one empty column.


    Another method is to define the range to be filtered:-


    This is the range you're interested in (it's also fully qualified with the worksheet variable (ws1):

    Code
    1. With ws1.Range("A3", ws1.Range("P" & ws1.Rows.Count).End(xlUp))

    and you'll note that the filter is still set on Column P(16).


    There are more options which can be used to do the same task but between this post and Gijsmo's post #14, your head is probably spinning a little.

    The method used is basically a Coder's preference.


    I hope you've been enlightened somewhat.


    Cheerio,

    vcoolio.

  • Hi vcoolio


    I've hit a strange snag with this. The archive sheet now has 270 entries where the macro has been used to take rows of data from the active sheet to the archive sheet.


    However it's now stopped transferring any further data. When the macro is run, it takes the data out of the row that has the "Yes" selected, does nothing then displays the message box, however this says 0 rows transferred, but the data just disappears , very strange... Any ideas please? I've tried to see if there is any limitations set on the number of rows but there doesn't seem to be.


    Kind regards


    DezB