copy contents of cell based on drop-down association

**Greed** · 10-04-2011, 10:04 AM

Hi everyone,

So I have a really complicated idea and I’m not sure if it’s possible, never mind how to do it…I have attached a spreadsheet for reference as this is quite difficult to explain.

The spreadsheet is like an attendance report, but sometimes employees are transferred to different locations. It is set up so in that event, the person filling out the report will select the new location from the drop-down, which tells the formula at the bottom of that location not to include that employee at that location anymore.
Problem is, you them have to manually enter that employee’s name in the next available cell of the new location.
I would like to have the employee’s name automatically transfer to the next empty cell of the new location – not be deleted from the existing location though, just shown in both places now once a transfer is selected from the drop-down list.

Hope you can make sense of this – please ask questions if I’m not being clear…I have partially completed the spreadsheet so you can see how it works. Thanks to anyone who can help!

**watersev** · 10-04-2011, 11:37 AM

hi, Greed, please check attachment

**Greed** · 10-04-2011, 12:10 PM

Hi watersev - not sure if I am missing something, or if something didn't get saved correctly, but I am not seeing any difference (no additional macros and does not seem to be copying the name of the transferred employee to the next available cell under the new hall...) where I have the 2 cells highlighted in yellow on the original spreasdsheet is my 'example' I need a macro to copy the name over to the other column once a transfer location is selected (H-6 was selected, so name should be transferred to the botton of the list under Hall 6)
thanks

**watersev** · 10-04-2011, 02:27 PM

it looks you have application events turned off, the code works as expected. Run this short code first and then try to change Attendance code for any employee. The code is triggered by changing attendance code.

Sub Enable_Events()
Application.EnableEvents = 1
End Sub

**Greed** · 10-04-2011, 03:15 PM

**Oh, sorry, I see it now - you rock!!!!

Hey - is that ever great...thanks!
Now how can I make that happen in my exiting documents (there are 4 different versions) I haven't a clue how you did it and since all of the existing macros/modules appear to be the same, how can I copy this event?

**watersev** · 10-04-2011, 03:24 PM

the code is not in the standard module but in sheet1 module

**Greed** · 10-04-2011, 04:11 PM

Again, many thanks! You have made some very 'computer illiterate' users very happy

**watersev** · 10-05-2011, 03:55 PM

please check attachment, sheet is protected, there is no password to unprotect. Try to change attendance code and then delete copied cell, hope this helps