I played around with this and had several thoughts on using change_selection and change to manage the transfers, but had a difficult time distinguishing between when I selected a value to cut it and when I selected a cell to paste it. Also I had to add in error checking to make sure you didn't select more than one cell, select a blank cell or paste over an existing member. So then I thought to let you arrange it the way you wanted and give you a button tied to a macro to sort everything out. That still gives you the opportunity to lose a member either just by cutting and not pasting or pasting over. Also you could wind up with duplicate members.
So I decided I needed the control that only forms can give. There is a button on the team sheet to launch the form. The form has two fields: Representative and Manager - both are combo boxes that are ultimately populated from the existing data. There is a helper sheet with some pivot tables on them to get the unique lists needed.
Select the Representative you want to move. Select the Manager you want to move the representative to. Click on the Assign button. This action will move the representative both on the Teams Sheet and on the audit sheet. The program also does a lookup based on the selected manager to get the correct Auditor. The whole affair is topped off with sorting the Audit Tracker table. I think it is fairly bullet proof and won't let you make the mistakes the other methods allow.
It's not exactly drag and drop. I'm not that good.
Bookmarks