VBA code to import pick'em picks into master sheet

Sorry should have made them match but I've been "enhancing" the pick sheet this year and just pulled an old sheet from last year for the master. They will match when I finalize it for the first week of the season. Hope that helps you, it shouldn't affect the needed code to copy and paste. Thanks for your time. -Chad

Ok...I do apologize apparently when I was trying to find out why you had a problem with the games not matching up I didn't count the columns right and added 4 columns that didn't need to be (my original had the right number, just the games didn't match). I have gone back and made the pick sheet and master file match for this year so now no teams play twice (Also I deleted my post earlier to maybe prevent getting anyone else confused by my mistake).

Attached are two cleaned up versions. I hope this helps make better sense. thanks again.

They can be the names of my files I attached above or can simply be one called picks and the other master.

First thank you for trying to help me I really appreciate it.

Ok first I'll answer your question. 30 our so people will be emailed the weekly picks file. They will then email them back to me with their selections made. Previously I'd open each persons file and copy and paste their picks into the master.

So I tried the code and it copied and pasted the weekly picks into the master but instead of just for one person it did this for all names. I don't know much about VBA but looking at the code you're looking at column L on the weekly picks and I'm not sure why. It appears you have the games and scores pulled correctly from the weekly but it should be looking at cell Z2 to get the name. Once it gets the name it should then find that person's name and what row in the master and paste their picks on that row in the master.

Hope this helps, thanks again. -Chad

The Weekly Picks file is each persons pick file. All the individuals see in the picks file is columns A-J. In cell C5 the individual will pick their name (currently just Name1, Name2, etc.) and then make their picks. In preparation to make it easy to copy and paste into the master I made columns AA-AN equal what they picked to be easier to copy and paste.

Each week I will go in and change the games in the weekly pick file and then send it out again so the format will not change to the individual.

If this still doesn't make sense let me know.

Ok I've changed a few things and the code now does what I want it to. While I'm sure there are things in the code not right (as it still has a reference to column L in the Sub Fill Master it works. If you can tell me what I can delete that would be great if not Thank you so much for your help. (Changes I made are blue)

Also, anyway I can make the target worksheet name equal a cell on the source file? Say I put Week 1 in Z1 on the weekly picks it will import into Sheet Week 1 in the master. If it Z1=Week 2 it would attempt to import to sheet Week 2 (which doesn't exist in my example file but will as the season goes on). If not I've tried and figured out if I just keep the current weeks sheet listed first in the master file it imports in the correct sheet. Thanks again.

Please Login or Register  to view this content.

Yes that is how the process works. I save the files in different folders for each week for example "H:\Desktop\CFB Pickem\Wk 1 Picks\" then next week picks go into "H:\Desktop\CFB Pickem\Wk 2 Picks\". As far as naming them (last year I didn't because i just open from their email and copied and paste) I was planning on just adding maybe their last name to the end of the file name so filename is sent out as "Games - WEEK 1.xlsx" so I might save them this year "Games - WEEK 1 - Johnson.xlsx". So yeah if there is a way to just tell it go look in Wk 1 Picks and run the process for all the files in that folder that would be great. Only that week's picks are kept in that folder so all files in their should go through the process. The master file and other files are kept in the folder one up "CFB Pickem".

Thanks again for your help.

Look this is the first time I've posted a question. I've always searched this forum and found answers I needed couldn't to this issue. I don't know the protocol, I felt the bulk of the issue was solved so I marked it as so. I think now we are just fine tuning it. I would like you to continue to work on what you were talking about where I wouldn't select indivual files it would maybe read all files in a folder. If I need to mark as unsolved I will.

I've marked it as unsolved. Please continue to help, would like to make this the best it can be and you've done a tremendous job thus far.

Magnificent! Great job! There was one small issue at first but after looking at your code I was able to find the problem. In the picks master file the sheet names were Week 1, Week 2 but picks were stored in folder Wk 1 Picks. So at first the picks master file would open and go to correct sheet but wouldn't do anything. Then I saw this code...

Please Login or Register  to view this content.

And it hit me the two being different might be the problem so I changed the picks masters sheet names to be Wk 1, Wk 2. It worked! So excellent job!!!

Now if you're tired of dealing with it I'll understand and you can be done BUT now that I don't click on the files individually my concern is say there are 30 people playing so 30 names listed in picks master file. Lets say there are 28 people (maybe 2 people were on vacation) who submitted picks and their pick files are in the Wk 1 folder. Of the 28 submitted picks 2 of the people forget to select their name in cell C5 (We've put some conditional format stuff and a box about is the pick fully completed Yes or No to limit this because this was a problem last year). So is there a way to get like an error report for it to pop up and say hey there are 28 files in the folder but only 26 were imported. Be even better if it could list the two file names not imported since we will be tacking on the last name to the files.

If it is not something simple or you don't want to fool with it, it is not a problem. We can just have who ever saves the files from the email to the folder open up that person's file and make sure it has their name prior to saving it to alleviate that problem.

Works like a charm. Last question I promise...is there any way to pull the data from the weekly files without actually having excel open them? Just trying to speed up the code because when there are 30+ files, you can see it open for a second then close then it does the next one workbook and it is taking some time. It doesn't really impact this instance becasue in theory we should only one the code one maybe twice for a given week. I'm really just curious if its possible. Thanks so much for all your help, you've really done an excellent job and I greatly appreciate it!

Ok no problem. Thanks for all of your help! Problem Solved!