Making an Excel Macro function across two open workbooks with changing file names.

Option Explicit

Public wkbScreening   As Workbook
Public wkbLocalSystem As Workbook
Public iErrorCnt      As Integer

Sub Main()

   Dim lRowCntr As Long
   
   lRowCntr = 2  '*** If no headers set to 1 ***

  GetFiles  'Get the two required files
  If iErrorCnt > 0 Then Exit Sub
  
  '*** your processing code here for example ***
  Do
    Cells(lRowCntr, 10).Formula = "=VLOOKUP(E2,[" & wkbLocalSystem.Name & "]Sheet1!$J:$J,1,FALSE)"
    lRowCntr = lRowCntr + 1
  Loop Until Cells(lRowCntr, 1).Value = ""
  
  '*** If you wish to close the files when done ***
  Application.DisplayAlerts = False  '*** Don't prompt for confirmation ***
  wkbScreening.Close (True)
  wkbLocalSystem.Close (True)
  
End Sub

Sub GetFiles()

   Dim zFileItems()   As String
   Dim zAllowedExts   As String
   Dim lNoFiles       As Long
  
   ReDim zFileItems(1)
   zAllowedExts = "*.txt"

   lNoFiles = GetFileToOpen(zSelected:=zFileItems, _
                            zPrompt:="Select Screening File", _
                            bMulti:=False, _
                            zExts:="*.xls*")
   
   If lNoFiles > 0 Then
     Set wkbScreening = Workbooks.Open(zFileItems(1))
     ReDim zFileItems(1)
 
     lNoFiles = GetFileToOpen(zSelected:=zFileItems, _
                              zPrompt:="Select Local System File", _
                              bMulti:=False, _
                              zExts:="*.xls*")
     
     
     If lNoFiles > 0 Then
       Set wkbLocalSystem = Workbooks.Open(zFileItems(1))
     Else
       iErrorCnt = iErrorCnt + 1
     End If
     
   Else
     MsgBox "User pressed Cancel or X (Close Box).", vbOKOnly, _
            "No Files Selected:"
     iErrorCnt = iErrorCnt + 1
   End If

End Sub


'                        +--------------------+                 +----------+
'------------------------|   GetFileToOpen()  |-----------------| 01/15/14 |
'                        +--------------------+                 +----------+
'Called by  :
'Arguments  : zSelected - a String array declared empty & ReDimed to 1
'             zExts     - a list of allowed extensions for the filter
'                         Ex: "*.xlsx, *.xls, *.xlsm, *.xlsb"
'                         Note: Only Excel filetypes as function is written!
'             zMulti    - True allows multi select, False allows single select.
'             zFileFilter - Optional - used to limit the files shown by name
'                         pattern, EX: "CA*.xls*" if ommited "*.xls*" will
'                         be used. Note: using "*.*" will over ride the zExts
'                         filter! You can also specify a drive/path to set
'                         the initial folder displayed.
'Notes      : You can uncomment the .Title line and supply your own
'             dialog box title and add an argument if you want to pass it!
'             You can uncomment the .ButtonName to supply a custom OK button
'             caption which can also be passed by argument if desired.

Function GetFileToOpen(ByRef zSelected, zPrompt As String, zExts As String, _
                             bMulti As Boolean, _
                       Optional zFileFilter As Variant) As Long

    Dim fd             As FileDialog
    Dim lCnt           As Long
    
    If IsMissing(zFileFilter) Then zFileFilter = "*.xls*"
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear   '*** Clear old filters just precautionary ***
        .Filters.Add "Spreadsheets", zExts, 1
        .InitialFileName = zFileFilter  '*** File Name Filter control. ***
        .Title = zPrompt
'        .ButtonName = "OK button caption"
        .AllowMultiSelect = bMulti 'Note: if not specified defaults to True!
        
        '.Show  Returns: -1 if Open button or 0 if Cancel button is pushed!
        If .Show = -1 Then
        
          ReDim zSelected(.SelectedItems.Count) 'Make array the proper size.
          
          For lCnt = 1 To .SelectedItems.Count 'Load the array with selections.
             zSelected(lCnt) = .SelectedItems.Item(lCnt)
          Next lCnt
        
        End If
        
        GetFileToOpen = .SelectedItems.Count
        
    End With   'fd
    
End Function 'GetFileToOpen