VBA code to import pick'em picks into master sheet

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