Copying data to open workbooks

Hi all,
I really was suitably impressed when I stumbled across this site a few weeks ago. I have recently started a job that requires alot of excel understanding so have been using this site as my first point of call to help me sort through several issues. Being the kind of chap who is keen to make a good example so they keep me on, I have been ecstatic when reading through some of the solutions on the site and all the excellent help many people are giving out for free- it just goes to show there are still some decent people left in this world...!

So my issue lyes in my lack of continuing to develop my coding skills after leaving education. I have tried for weeks to try and get my head around this but to no avail.

I'll let you know my issue- I have 5 workbooks open at once(named BOOK1, BOOK2, BOOK3, BOOK4 BOOK5, lets say) each have several named tabs with 200 rows of data in each tab. Each line within, is a project that may or may not have been completed and scored yet but already has a VIN(individual number) allocated to it. When they are scored, the VINs are provided in a 'new' update sheet and I am to input the scores on each of the corresponding rows within the 5 master workbooks.

So, as there are 10000 projects, is there anyway to run a macro that takes each VIN from the update sheet, find the corresponding VIN on the right tab on one of the 5 open workbooks and replace empty cell to it's right from the score to the right of the VIN in question from the update sheet.

Not entirely sure how clear that is...?

Say the update sheet was as simple as A1= VIN1 A2= VIN2, etc and B1=Score1 B2=Score2, etc. Can I run a macro to find 'VIN1' from any of the 5 open workbooks and replace the cell to it's right with 'Score1'(then VIN2, VIN3 etc)

I do hope that's clear- as I said I have been pulling my hair out over this for weeks now and with 10000 VINs to keep up with, I feel I may never get to prove myself doing it manual way.

Thankyou so so so much indeed, in advance if you do feel like you have a couple of minutes to reply- you have no idea how much it'll mean to me...! Remember my skills are somewhat lacking... hehe.

Keep up the good work chaps and I look forward to any pointers.

Respectfully yours,

Laurenz

Mike,
Thankyou eternally for the quick response- very impressed. It seems like you definitely know more than I do so thankyou for your time in trying to help me out. Not sure if I was meant to but as it was returning an error on line:

Please Login or Register  to view this content.

With this message:

Compile error:
Expected End Sub

I changed it slightly to the colomn headers I'm using:

Please Login or Register  to view this content.

On the update workbook I get provided with, I put this code in sheet1 (02-06), ThisWorkbook and a newly created Module1, as am not sure where it should belong. When I run the 'RNG macro' it returns with an error '91': Object variable or With block variable not set' highlighting this line when debug is clicked:

Please Login or Register  to view this content.

This is similar for the 'Sheet1 (02-06) macro' but interestingly, when I run the 'ThisWorkbook macro' it scrolls through all the other open master workbooks for about 20 seconds but doesn't copy the contents into the corresponding cell. As you have probably worked out by now, my skills are somewhat lacking to say the least so I am so so so thankful to you for taking the time to help me out- you really have no idea how much it means to me and the difference it will make to my life. Perhaps I could send or upload a copy of the workbooks?

Many thanks once again,

Laurenz

Hey Mike,
Wow- I can see why you go by the name realniceguy5000, you really are very kind. I had a look at the script but couldn't quite make sense of it- very clever indeed. It kinda works but returns an error here:

Please Login or Register  to view this content.

I've uploaded a revised 'TEST' folder for your perusal- I hope it makes sense. They give me update sheets that may only have a selection of the ones provided on it(for example)

Vins: 1 4 6 7 10, etc

I really can't thankyou enough for taking the time to help me out- it's going to have a huge effect and I'm so excited about it all. Many thanks once again.

Respectfully yours,

Laurens

Dear Mike,
You've probably heard this before but... YOU ARE A GOD AMONGST MEN....! I copied the code into my update sheets and it works a treat doing EXACTLY what I needed. You have no idea how much you have helped me out and the effect it's going to have on my new job. You have fully restored faith in human mankind- thankyou, thankyou, thankyou.

It has halved my input time, the only thing I have to do now is go through any of the ones that had comments attached to them and make sure I copy these across manually. I've had a look online and believe that copying the comments may be quite difficult- although, you are a genius, so you may just have a magic trick up your sleeve. Just thought I'd mention it before putting it to bed.

Once again, keep up the 'realniceguy' persona, perhaps 5000 isn't enough... hehe. Have a good day and I look forward to hearing from you as to whether or not to bother trying to get the comments to copy across aswell. A 'solved' and 'reputation' score will follow shortly.

Respectfully yours,

Laurens

Mike,
Wow- I wasn't expecting that...!
Really just the same as what we were doing before. The scores on the update sheet, very often have comments attached to that cell and these need to be transferred to the corresponding cell on the master record workbooks- just along with the score being copied across. Don't know if I'm explaining myself clearly but I would be completely over the moon if you did have some sort of solution- perhaps that incorporated into the original update 'command_button' you did on the update sheet. A thousand thousand thank-yous...!

Respectfully yours,

Laurens

Hey Mike,
Hope you're having a nice day. Apologies for the slight delay in my reply- I have been off with some strange bug. There I was thinking I'm invincible to germs. Anyways, everything is running smoothly with the new code and I am eternally grateful to you for your help- it's made such a great difference...! I've spent quite a bit of time trying to copy your work so as to create a second button that does everything in reverse. So basically, when I click it, it checks all the open workbooks and if a score is already found for that vin, returns the score or a 'X' onto the update sheet, to tell me that that one does not require any quality checking. I would then be able to remove these from the update sheet before doing my work and sending the new scores back out, when I click on the update button.

Not sure if I'm making sense, just thought I'd mention it as I've spent hours trying to figure it out, whereas it might just take you a few seconds. Really don't worry if you don't have the time to help with this one- you've been so kind already...! I'm sure I'll get my head round it one day... or not as the case more likely is to be... hehe.

Anyway, have a lovely weekend and keep up the good work buddy.

Laurenz

Mike,
Sorry for the delay again- they've been piling more projects on me as I think they are quite impressed. A lot of that is down to you so thankyou.

I tried the new sheet and it looks like it's exactly what I need and will make a huge difference As they've just doubled my workload...!

On both the new buttons, it's returning an error at line:

Please Login or Register  to view this content.

I've had a look, but just can't get my head round it, whereas I'm sure it'll take you two seconds. Just thought I'd mention too, not that it matters as if there's a mistake then I can just not save the updated worksheets, but the reset buttons don't work as the 1st one replaces all the scores that were already there with '999.' Not to worry though as, like I said, I can just not save the updated worksheets. I really can't thankyou enough for being so patient with me- we're nearly there. Do you have a paypal account that perhaps I could buy you a pint or something- I'm just so grateful.

Many thanks,

Laurens