Move row to another sheet based on cell value

Hi Everyone,

I've seen multiple threads on that topic, and I've tried tweaking my code but there is something I am still unable to do.
I would like to move a row in Sheet1 to Sheet2 if the value in column J is "Changes Required".

My code below seems to be working when "Sheet2" is empty, but when I have a header in Sheet2 with content up to row 5, the deleted row from Sheet1 does not come over Sheet2. It just disappears. My guess is that it's trying to paste in row 1 and since there's content in it, it does not work.

Also, I'd like my code to run automatically rather than having to manually click on Run.

Please Login or Register  to view this content.

Thanks!
M5B

Try:

Please Login or Register  to view this content.

Hi Mohan,

I tried with this line and it does not work. The row does not come over to Sheet2.

This seems like a tough one!

Thanks,
M5B

Originally Posted by M5B

... and nothing happens ...

You have a different table range with data, can you specify it ?
The rows and columns of the macro must be adapted to the scope of the data.

Hi Mohan,
This does not work, it does not even delete the rows in Sheet1.

Originally Posted by porucha vevrku

You have a different table range with data, can you specify it ?
The rows and columns of the macro must be adapted to the scope of the data.

I am not sure I understand what you are asking?

As mentioned in my previous screenshot, Sheet1 and Sheet2 are duplicate in terms of structure. There is copy up to A4 and the data I want to move starts at A5.

How many columns does the table have ?
Is there a minimum one-column gap between the data in the last column of the table and the rest of the data in the worksheet ?

Originally Posted by Trebor76

Are you sure there's entries in Col. J of Sheet1 that say "Changes Required"? In your original post the code is only looking for text that says "Revisions Required" so do you need both transferred?

There's a "TBD" in my example. And when I type "Revisions Required" and run the macro, it deletes the row but does not move it to Sheet2

I am not sure how to upload an Excel file so I've included 2 new screenshots. 1 is Sheet1 and 1 is Sheet2
There are empty cells on Sheet1 - would that be the issue?
Attachment 601782
Attachment 601783

Originally Posted by Trebor76

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Note this worked on sample data for me.

Regards,

Robert

Hi Robert,

I'll do some proper testing on my file tomorrow, but so far it seems to be working.
Your VBA code looks very complex though, I'm having a hard time understanding (I like to understand how things work and how each line actions something)

Thank you so much to everybody that helped!

Hi Robert,

So I've been working around the code trying to add a second criteria and I could not make it work.

I'd like to add a second criteria for the same column, J, so that if I type "Ready for delivery" it sends the line to Sheet3. If possible, I'd like it to be auctioned through the same macro (I run it once and it moves the lines to Sheet2 or Sheet3 based on the value in column J)

Thanks
M5B

Works perfectly, thanks so much Robert!