Move Row To a Particular Sheet Based on Cell Value

I have created a simple inventory of my DVD's and Cd's. Three sheets in my workbook, At Home, Loaned Out and On Hold. Column C, in each sheet, which is named Status has a dropdown list with three choices, happen to also be At Home, Loaned out and On Hold. What I am after is when I choose the dropdown list I would like that row to be moved to the corresponding sheet. My current VBA will move the row from At Home sheet to Loaned out, can't figure out how to get them to move back and forth. This is the code that is attached to the At Home sheet, when I put this on the Loaned Out sheet and change it accordingly it doesn't work. Any Suggestions?

Please Login or Register  to view this content.

Hi there,

See if the attached workbook does what you need - it uses the following code:

ThisWorkbook VBA CodeModule:

Please Login or Register  to view this content.

Standard VBA CodeModule:

Please Login or Register  to view this content.

I've modified things a little in that the dropdown list in each worksheet shows only the names of the two other worksheets - i.e. you don't have the option of transferring a data record from one worksheet to the same worksheet.

Also, the data records in the source and target worksheets are sorted by Album Name after each transfer operation.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Wow, that is to the point and works great, thank you, very much appreciated!

Thanks Greg M., that is awesome work. I thank you for taking the time to code it but mostly I appreciate the time you took to explain how each line works. I'm slowly learning the language and it helps when I can follow the process in my head, it allows me to use all or pieces of the code later in other work. Thanks again, it worked perfectly!

Hi again,

Many thanks for your feedback, for taking the trouble to thank me not once, but twice, and also for the Reputation increase - much appreciated

I'm very pleased that you found my post so useful.

Best regards,

Greg M