How to keep track of changing teams without losing data.

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.

You could possibly export and re-import the modules, but even then you'd have to assure that the sheet names remain the same and make sure you have the named ranges, List_Manager and List_Rep established.

It may be a lot easier to remove the data on the Audit Tracker table and then copy/paste your data into it.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

In the attached, I threw in a module to clear the table and associated it with a button on the Audit Tracker sheet.

Once you paste in your new data (or edit the Audit Tracker Table for that matter), refresh the pivot tables. Data->Refresh All should do this or you can right click on either table and select the Refresh option from the menu.