VBA: Looking for a Macro to Match and Copy from Source to Destination Sheet

[AliGW]

I am wondering if anyone could help me with this? I have attached a file with two sheets: source and destination.

Cell D5 on the source sheet contains a drop-down with a list of named ranges that have been defined on the destination sheet. I would like to have a macro assigned to the button on the source sheet that will copy from the list in column D on the source sheet to the relevant assessment column (Y7_M2 has been selected) on the destination sheet. The macro needs to copy data for the correct student: the unique identifier is in the ID column on each sheet. The lists on the two sheets may not be identical: there may be students missing in the source list, and there may even be extra students who do not exist in the destination list. The extra students can be ignored: they do not need to be added to the destination list. If a student in the destination list does not have a match in the source list, then nothing should be written to his/her cell in the destination column. The names of the two sheets will remain constant: they will not be changed.

As a complete novice, I'm not sure where to start and I have no idea how complicated what I am asking may be - sorry. I know how to record a macro, but as the lists may not be identical, doing it this way would be impractical. I also know how to do an INDEX MATCH to get the information from one sheet to the other using a formula, but the idea is to use the source sheet to populate the destination sheet each time an assessment has been taken. I hope this makes sense! Thank you for any and all advice.

[AB33]

Hi Ali,
I am not sure which columns or what data you want to copy over from the source sheet. The code copy one cell only, but can be modified. I can also add an event code which can auto trigger the code. Right now, I am tied up and will check the site in the evening.

[AliGW]

Thanks. Sorry, I should have made clear that the data I want to copy is just the assessment data (the grades). How can this be modified to copy across all grades for matching students to the destination sheet? It is doing what I want, but only for the first student. Thanks again for the help!

[AB33]

Hi Ali,
Try the attached. If the out put is not correct, perhaps, you can add the Index and Match formula on the destination sheet so that I can use it as a guide.

[AliGW]

Thanks so much! This works fine in terms of the match. The only problem is when I change the destination range using the drop-down and copy again: for some reason, the data that has already been copied gets deleted. What I need is for any data that has already been copied into other ranges on the destination sheet to stay put. Does this make sense?

[AB33]

Yes, you are right! I was about to change it before I saw your post. The code is attached to copy button. Just change the drop down in D4 and press the copy button. You can also run it as auto-trigger code, i.e. as soon as you make changes to D4, the code will run. In other words, the code will be driven by change events, but you can not do this if you want to use the button.
The code is sensitive to change in the format of the data, i.e. change in rows and columns can give you the wrong result. If you want to make change you need to drop me a PM as I will forget this thread in few weeks time.

[AliGW]

Thanks! I'll take a look in the morning and let you know how I get on. As this is only a test file, there will be inevitable changes to rows and columns when I deploy this properly, so I'll need to know what to change in the code. I'll see if I can work it out in the morning. I do want this to run using the button. Thanks again!

[AliGW]

Thank you so much! This works brilliantly. I have looked at the code and confess to being flummoxed! Is it too much to ask for a version of it with an explanation of what each bit does? I might then stand a better chance of being able to adapt it myself for other workbooks. There is just one more thing that I feel I need: is it possible for the code to check if there is already any data in the chosen range before copying? And if so, can it throw up a message checking if the user wants to overwrite the data that is there? You have been really helpful - thanks again!