I've created two spreadsheets used for a friendly workplace college pick'em. One spreadsheet is sent out to everyone to send back with their picks and the other is the master where all the picks are compiled. In the past I've opened each person's pick file and copy and pasted their picks in the the master. I'd like to see if some VBA code could make this a lot simpler. I've attached my two spreadsheets (Updated: deleted attachments see lower posts for attachments).
I would like the code to prompt me to tell it what the master file is and what the weekly pick file is (and after importing one set of picks ask me for another pick file until complete). What I struggle with is how to get the code to match the person's name in the pick file to the master file and paste their picks in that row.
The code should find the person's name in the weekly pick file (Z2) and find the matching row of data in the master (somewhere in Column A starting in row 3). Once it is found copy their picks in the weekly pick file (AA2:AN2) and paste on the approriate row in columns B-O. Would also like to copy their tie breaker scores in their weekly picks (AO2 & AP2) into the appropriate row in the masters in column R & T respectively.
Few notes: There will always be 14 games that are picked and there will always be two games scores used for tiebreakers. The weekly pick will always have the same setup and sheet names. The master file sheet name will change each week (Week 2, Week3, etc.) so some option to change the code each week would be nice.
I appreciate any help anyone would be willing to give. Thanks in advance. -Chad
Bookmarks