Checking then copying data to open workbooks

[lauzer]

Hi all,
I've tried re-writing this post to make it simpler but am not sure I can do anymore. This site has been a life-saver to me and some of you guys out there really are amazingly nice people so thankyou in advance if you can spare a couple of minutes to read this post.

I had a very nice chap help me out on a dummy workbook(attached) but he appears to have disappeared in my time of desperate need. Thus I am searching for some kind soul to simply add a couple of lines of code to finally get it working.

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 400 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 20000 projects, we're trying to have a button(CommandButton1) that will take each VIN from the update sheet, find the corresponding VIN on the correct tab on one of the 5 open workbooks and replace the two empty cell to it's right with the score to the right of the VIN in question from the update sheet. As some of the projects are already checked by other people(610 & 620), we then tried another button(CommandButton5) to run first that checks the 5 open workbooks first and if it already has a score then delete that row on the update sheet so as to not re-check it.

CommandButton5 does exactly what is needed. The thing that is missing is with CommandButton1- rather then JUST copying the score across, I also need it to copy the 2nd score in the next column as well, as each VIN has 2 seperate scores to them.

I do hope that's clear- this has been driving me mad for weeks now and, what with being so close to success, I would be eternally grateful to anyone that has a spare minute to quickly look at the code on the update sheet, and put it right. You have no idea how much of an impact this small bit of code will have on my life and future prospects of being kept on at this lovely workplace, when my temporary contract expires.

Respectfully yours,

Lauzer

[realniceguy5000]

Hi, Been A While...

I dont see a second score on any worksheet with people names?
I dont see a second score on the master worksheet either... I do see a book name on the on the master : update worksheet? Is this what you want placed next to the score on each worksheet with people names?

We are talking about sub RNG that has the issues correct?

It should not be hard once I get a picture of what you want where.

...

[lauzer]

MIKE...!!!
Oh my golly gosh- my savour...! I thought I was slowly drowning but you've risked your life and jumped into save me... thank-you, thank-you, thank-you...!

I must have attached the old version that you last did for me so have included the new one I've been playing with.

I really just need 2 buttons. One to check the 5 master sheets and if they have already been scored, to remove that row from the update sheet. The ones left can then be scored and commented on. So a second button to transfer all the new scores and levels onto the empty spaces on the 5 master sheets. The reset buttons were returning the value of 999 but this isn't so important as if I mess up, I can simply close all the workbooks without saving. The reset buttons are a lovely idea but you've done so much for me all ready, they really aren't as important.

I did have a slight issue when I was testing them out whereby it would error, as some of the scores were letters and not just numbers. If letters aren't allowed, then I can simply use numbers instead so again isn't a huge biggie.

Mike, thank-you so much for coming back to this one- I understand you must be a busy man and so am eternally grateful for this last little change. I kinda had a feeling you'd be back- you are a 'realniceguy' after all.

Respectfully and thankfully yours,

Laurens

[realniceguy5000]

Here is a new version for you to try, Lets just take small steps because this gets crazy... lol

I only worked on the first script command button 1 this loops through all the workbooks and each worksheet looking for the vin # on the master sheet. once found it will populate the worksheet :cell with the 1st score the second score and any comments found on either score.

If this solves this issue lets move onto the next script. I wasn't clear what you wanted to do next...

As far as the resets these were test resets, meaning they just reset the data back to what it was before I ran my code. No real value in those except I could reset and test quicker.

Let me know about this update version and we will either fix again or move onto the next script.

,

[lauzer]

Morning Mike,
I was actually really excited to get to work this morning as I had a feeling you may have been kind enough to have a look already- again, you can't believe how much of a difference this is going to make and therefore how much it means to me so, thankyou once again.

As far as I can see CommandButton5 is working but CommandButton1 isn't copying the 'level score' accross. I have updated ALL of the sheets in the hope that it makes it a little easier to work with inline with what I'm doing and put some instructional comments on the 'master update sheet,' so it's all in one place. Let me know if it's clear or not.

Not sure if I need to put it onto my workbooks that I'm working with but thought I'd mention that you have put a bit of code on Book5. Let me know if this is needed for it to all work or if I can ignore it.

I can't thankyou enough Mike- probably getting a bit annoying how much I gush with thanks so I'll attempt to keep a cool head going forward.

Cheers buddy,

Laurens

[realniceguy5000]

Ok,

Test Button 1 is working as far as I can tell at least with the test result workbooks. 'Please let me know if this is not the case.

Test Button 2 Has been updated and now is working ' Please let me know if this is not the case

Test Button 3 I have not worked on this button just yet in this rev2 workbook. I just wanted to make sure you want to delete the entire row of the update workbook. The reason I bring this up. it is possible that one score will match your results and the 2nd score will not? yes?

I'll make some modifications to test button 3 and post rev3 later today. But we may need to edit this script (Test 3) just a bit to handle any issues that come up.

As far as the reset buttons I removed those because they serve no real purpose any longer.

Also I fixed the letter issue, I made a slight error causing the script to report back only numbers,so that should be ok now. Will see

Keep me posted on the issues you see.

[lauzer]

Mike,
I had time to test it in my lunch hour and confirm that it is behaving the same as before.

My process is as follows:
1.
On the update sheet with just VINs and no scores or level yet, I click Button 3 which checks the other workbooks and removes that row on the update sheet if there is already a score for it in the open workbooks, so I don't need to score those as they are already done.(Only checking if there is already a score is important as they will only have a level after being scored anyway.)

2.
I then enter scores and a level for the VINs left on the update sheet and add comments.

3.
Lastly, I click on Button 1 to transfer all the new scores and level from the update sheet to the other workbooks along with the comments attached to each cell.

Everything is working brilliantly, apart from the fact that it only copies the score across and not the level. It copies the comments for both the score and level, but not the number in the level cell. Button 2 is not needed at all as this is just an update sheet and the final scores are to be kept on 'the other workbooks.' Do I need that line of code in Book5 at all or should it just be ignored?

It's probably a really simple change but waaaaay beyond me. I'm getting so excited that we're almost there. Is there any more I can provide other then the 'scales' reputation and [SOLVED] selection?

Once again, cheers buddy,

Laurens

[realniceguy5000]

Ok Here we go, Test and post back...

Retested test 1 seems to be working.

Removed test 2 button and code

Tested test 3 seems to be working but not sure if does everything you wanted.

The code in book5 is nothing.

Thanks Mike