Cut row and paste to another worksheet next avaliable row

[Bramjonker]

Hi all,

I love this forum and have learned so much already. But I am stuck on something and I really hope someone can help me with this! I am trying to write a macro for the following; When the cell value in column M changes to Yes (in worksheet "Prospects") then it should cut the entire row (the row of the changing value to Yes) and paste it to the next available line in sheet "Macro". With my current macro it does only copy's the row and sorts it. What I would like is that it cuts the row, paste it to the next available line wihout sorting the sheet. This is my VBA code;

Please Login or Register  to view this content.

Hope someone can help me! Thank you in advance

[jeffreybrown]

This is workable, but first, take a few minutes and read the forum rules about code tags.

Once this is completed we can proceed with a solution.

[Bramjonker]

Hi Jeff,

Thanks for your reply. I read the rules and think I have to make the following adjustment, please let me know if I am wrong. Thank you for your help and I really hope you can help met out! Thanks

Please Login or Register  to view this content.

Hope this is better Thanks in advance

[jeffreybrown]

Thanks for the update.

Right click on worksheet tab Prospects, View Code and paste on right side of screen.

Everytime the word "Yes" is entered in column M, that row will be cut to the next available row in the Macro sheet.

Please Login or Register  to view this content.

[Bramjonker]

Hi Jeff,

It works amazingly! Thank you so much for your help! Great forum with great people! Have a nice day

Greetings,
Bram

[jeffreybrown]

Hi Bram,

Thanks for the feedback and glad it worked out for you.

Please don't forget to mark your thread as solved.

[Bramjonker]

Hi Jeff,

One more question if you don't mind. Would it be possible to hide the row when column M changes instead of deleting the row (as it is cut)? If possible, can you help me adjusting the script?

Greetings,
Bram

[jeffreybrown]

If "Yes" is entered in column M, do you want both to happen? Row moved to the Macro worksheet tab and also hidden.

What about unhiding the rows at a latter date?

[Bramjonker]

Yes, it would be great if both can happen. So copy to next available row on macro sheet, and hide row in Prospect sheet

[jeffreybrown]

Try...

Please Login or Register  to view this content.

This will unhide all rows.

Please Login or Register  to view this content.

[Bramjonker]

Jeff, it is perfect. Thank you so much. I will mark this thread as solved.

Have a great day

[Ernest1974]

Hi guys,

I've followed your instructions but it doesn't work for me.

As you can see, i only changed the tab name "Macro" for "Done" and the word Yes for Done, and it seems to clash in the very last order "Sheets("Done").Range ("A" & Sheets("Done").Cells(Rows.Count, 1).End(xlUp).Row + 1)"

Any suggestions on what i'm doing wrong?

Many thanks!!

Ernest

Please Login or Register  to view this content.

[jeffreybrown]

Hi Ernest1974 & Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Please alse refer to the forum rules about using code tags when posting code.