Creating an automatic move to another sheet when a specific option is chosen from list

I need to create a macro I believe--to have a row move to another sheet once a status is chosen to represent that row. For instance we have about 8 columns in each row with information on one client. The first column is named "status" denoting the status of client IE (Signed, Not Signed, In Progress) I want only the "In Progress" to remain on that first sheet. The other sheets in the document will be labeled Not Signed and Signed. When I change the status from a list in the first column of "status" from "In Progress" to "Signed" I want that entire row to MOVE to the sheet that is named "Signed" and then delete from the current sheet we were working in. Thank you in advance!

Hi,

Try inserting this subroutine into the Worksheet module of the first worksheet that you are referring to:

Please Login or Register  to view this content.

This may require some changes for your particular circumstance in your spreadsheet, but take a look at the attachment and hopefully you'll get the idea.

Let me know if you get stuck and I'll see what I can do to help out some more

Thank you both--please bare with me as I am not as fluent in excel and its modules/macro as I am sure you guys are. I did try inserting this into a module on the current page (in progress) however, it doesn't change when the status changes. I think it could be how I set up the list options. I only knew how to set it up by having them created in a separate page, highlighting them, giving them a value and then validating them. Is there a better way to do this? I also tried copying/pasting and dragging what you did in the workbook that you sent as a sample and it basically voids the macros and will not work. Also, do I need to start from scratch on the page or will it work (if done correctly) with my current data after I have the module in correctly? THANK you so much for helping thus far...I am not one to give up but I feel as though I am over my head.

I'm not 100% sure what you're describing, however I would suggest taking then workbook that I attached and using it as a template.

In other words, copy all of your data and paste it into the "In Progress" worksheet in my workbook and see if you can make that work.

The other thing that I might suggest is just to ensure that macros are enabled for the workbook, as it will not function properly with macros disabled.

Good luck, I will check here again in the morning to see how you got on with it.

Potential Client Tracker Edits.xlsHi again, I did try copying and pasting but it didnt work. I also made sure the macros were enabled. I have attached my sheet so you can see what I am talking about when it comes to the "Status" column. Thank you again for all this help. It is nice to know there are so many people willing to help those of us that need it.

Have a go at this one Morgan.

1. I have implemented my code into the workbook so that it works.
2. With your validation list, you can simply enter the values directly into the formula inside the validation options, rather than referring to a named range like you had done, so I have tidied that up for you also.

Let me know if this helps

AHH almost there. It works for "Signed" but not for "Not Signed" i think it is missing the directs in the module. The row disappears when I select "did not sign" but it doesn't show up in the tab. Is it because the tab is a different name? You have no idea how much this helps even to this point. THANK YOU THANK YOU

Hi again,

I've just looked into this for you. What is actually happening is the "Did Not Sign" is being copied to the correct sheet, but because you have a lot of used rows much further down the sheet, the new row is being pasted at the bottom of that data, as opposed to just beneath the headings in row 1. You will need to delete all of the data down below (or move it up to start at row 2), and then the workbook will function as you intend it to.

Please don't forget to also mark this thread as solved, and click on the * to the bottom-left of my post to say thanks

Ok so you think I should delete the rows below the last input that are outlined to make it work? If so--will do and pray it works. Thank you.

You're welcome.

If you're a little unsure about deleting the data, I would make a duplicate of the "Did Not Sign" worksheet and give it a different name just in case something goes wrong - at least then you have your data backed up until you can confirm that everything is working perfectly.

Have a great day