VBA code to import pick'em picks into master sheet

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

Hi chadwick140

I'd be willing to play with this but I don't know how to approach it when the Games in the Weekly Picks aren't the same Games as in the Master File. What am I missing?

Hi Chad

Ok...perhaps another will look at this with you. I'll not write the Code and publish it without testing. I'm unable to test with your attached files. Sorry...

Content deleted by poster. Incorrect information.

Hi Chad

It matters not to the Code but I'm curious...why is Mississippi Sate playing two different opponents on the same day?

Oh, by the way, you have 18 Games in the Master and only 14 in the Weekly Picks. Please post files that represent the same structure as you're actual Files or any Code we write may well fail. Again, this may not matter to the Code...it could matter to you.

You do the front end work then we'll try to make it work for you...please clean up you're sample Files then we'll work on it...Garbage in...Garbage out...

Hi Chad

What will be your Workbook Names?

Hi Chad

I have the Code written to update Master from Weekly Picks. What I still need from you is to explain how each individual's Weekly Picks get inputed into the Weekly Picks Workbook...what's the Source of that information?

Here's the Code as it currently stands.

Public sWB As Workbook
Public sWS As Worksheet
Public tWB As Workbook
Public tWS As Worksheet
Public fname As String
Public fname1 As String
Public LR As Long
Public Rng As Range
Public cel As Range

Option Explicit

Sub Open_Master_File()

    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\Documents and Settings\Administrator\Desktop\chadwick140\New Stuff\Target Files"   'this is the default folder shown
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"        'everything
        .Filters.Add "Excel Files", "*.xl*", 1  'default
        .Show
        If .SelectedItems.Count > 0 Then
            fname = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Set tWB = Workbooks.Open(fname)
    Set tWS = tWB.Sheets(1)
    Call Open_Weekly_File
End Sub


Sub Open_Weekly_File()

    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\Documents and Settings\Administrator\Desktop\chadwick140\New Stuff\Source Files"   'this is the default folder shown
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"        'everything
        .Filters.Add "Excel Files", "*.xl*", 1  'default
        .Show
        If .SelectedItems.Count > 0 Then
            fname1 = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Set sWB = Workbooks.Open(fname1)
    Set sWS = sWB.Sheets(1)
    Call Fill_Master
End Sub


Sub Fill_Master()
    Dim myName As Range
    sWB.Names.Add Name:="Names", RefersTo:= _
            "=OFFSET('" & sWS.Name & "'!$L$6,0,0,(COUNTA('" & sWS.Name & "'!$L:$L)-1),1)"

    With tWS
        LR = .Cells(3, 1).End(xlDown).Offset(0, 0).Row
        Set Rng = .Range("A3:A" & LR)
        For Each cel In Range("Names")
            Set myName = Rng.Find(cel, , xlValues, xlWhole, xlByRows, xlNext, False)
            If Not myName Is Nothing Then
                sWS.Range("C5").Value = cel
                myName.Offset(0, 1).Resize(1, 14).Value = sWS.Range("AA2:AN2").Value
                myName.Offset(0, 17).Value = sWS.Range("AO2").Value
                myName.Offset(0, 19).Value = sWS.Range("AP2").Value
            End If
        Next cel
    End With
    sWB.Close False
End Sub

These two lines of Code will need to be changed to suit your environment.

.InitialFileName = "C:\Documents and Settings\Administrator\Desktop\chadwick140\New Stuff\Target Files"   'this is the default folder shown

.InitialFileName = "C:\Documents and Settings\Administrator\Desktop\chadwick140\New Stuff\Source Files"   'this is the default folder shown

Hi Chad

This is the piece I'm missing

I'd open each persons file

What I want to do is get each persons picks into the Weekly Picks File...the Code will then transfer each persons picks into the Master.

Depending on the Structure of each persons Pick File, we may be able to bypass the Weekly Picks File altogether. At the moment the Code has no way to discern the individual persons Picks. Need to know where to get them.

If you please, show me what an individual Pick File looks like.

Hi Chad

Let me understand your process...

Each week you Email the Weekly Picks File to each Name. This File is of the same structure as the "weekly picks Rev2.xlsx" I've been working with; only difference is Columns K through AP are hidden.

The Name selects his/her Picks and Emails the File back to you. You download and save each of the attached Files. Where do you save the Files as they're returned and how do you name them? What I wish to do is grab each of these individual Files, loop through them and process their Picks to the Master File. The Code will find and open each of the Files, one by one, and post to the Master. No selecting required.

Regarding this

anyway I can make the target worksheet name equal a cell on the source file

Yes, this is possible. I'll think about it and make a recommendation. Please get back to me.

Hi Chad

Is it SOLVED? Shall I stop working on it?

Hi Chad

This is your call

If I need to mark as unsolved I will

Most members of the Forum, including me, will not look at a SOLVED Thread except for insight into solutions they're researching.

If you wish it fine tuned to work as you'd like...use your judgment...I'll continue to pursue it...if not...glad I could help. Let me know.

Hi Chad

The following Code is in the attached Code Book v2.xlsm along with a UserForm. The UserForm will ask you to Select a Week to Process. I'm attaching six files (five attached to this Post...the sixth will be attached to a follow-up post)

They should be placed like this Directory Tree

Folder H:\Desktop\CFB Pickem\ <------ Call this whatever you like...put it wherever you like

Code Book v2.xlsm

Target Files\

picks master.xlsx

Wk 1 Picks\

Games - Week1 - Harry Cary.xlsx

Games - Week1 - Joe Johnson.xlsx

Games - Week1 - Mary Mary.xlsx

Games - Week1 - Pete Smith.xlsx

Run the Code from the Button in Code Book v2.xlsm. Let me know of issues.

Public sWB As Workbook, tWB As Workbook
Public sWS As Worksheet, tWS As Worksheet
Public LR As Long
Public Rng As Range, cel As Range
Public myPath As String, myFile As String, PickName As String

Option Explicit

Sub Open_Master_File()
    Application.ScreenUpdating = False

    myPath = ThisWorkbook.Path & "\Target Files\"
    Application.Workbooks.Open (myPath & "picks master.xlsx")
    Set tWB = ActiveWorkbook

    UserForm1.Show

    If IsNull(UserForm1.ListBox1.Value) Then
        ActiveWorkbook.Close False
        Exit Sub
    End If

    Set tWS = tWB.Sheets(UserForm1.ListBox1.Value)
    tWS.Activate
    Call Open_Weekly_File
End Sub


Sub Open_Weekly_File()

    myPath = ThisWorkbook.Path & "\" & UserForm1.ListBox1.Value & " Picks" & "\"
    myFile = Dir(myPath)

    Do While myFile <> ""
        If myFile Like "*.xlsx" Then
            Workbooks.Open myPath & myFile
            Set sWB = ActiveWorkbook
            Set sWS = sWB.Sheets(1)
            PickName = sWS.Range("C5").Value
            Call Fill_Master
            ActiveWorkbook.Close False
        End If
        myFile = Dir
    Loop
End Sub


Sub Fill_Master()
    Dim myName As Range

    With tWS
        LR = .Cells(3, 1).End(xlDown).Offset(0, 0).Row
        Set Rng = .Range("A3:A" & LR)
        Set myName = Rng.Find(PickName, , xlValues, xlWhole, xlByRows, xlNext, False)
        If Not myName Is Nothing Then
            myName.Offset(0, 1).Resize(1, 14).Value = sWS.Range("AA2:AN2").Value
            myName.Offset(0, 17).Value = sWS.Range("AO2").Value
            myName.Offset(0, 19).Value = sWS.Range("AP2").Value
        End If
    End With
End Sub

Hi Chad...attached is the sixth File