VBA code to import pick'em picks into master sheet

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