Macro to Add VLOOKUP to Lookup Another Workbook in Network

[vp83]

Hi,

I'm trying to add to the following working code in a workbook (File1) that will prompt the user to browse for the vlookup reference file (File2) saved in the network drive, add/autofill the vlookup formula in Column S until the last row there's data (File1) and then save (File1) in another folder in the network with the current date (MMDDYYYY) in the filename.

So far I only have:

Dim t As Date
    t = Now()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim myDir As String
    myDir = "\\Network\Staff_Documents\AcctName"
    CreateObject("WScript.Shell").CurrentDirectory = myDir
    Set wb = Workbooks.Open(Application.GetOpenFilename)
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    With Selection
    Selection.NumberFormat = "0"
    .Value = .Value
    End With
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("File2.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    wb.Close False
    Set wb = Nothing
    Set ws = Nothing
    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("A:R").Select
    Selection.Sort Key1:=Range("R1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("R1").Select
    MsgBox "Request Completed in " & Format(Now() - t, "hh:mm:ss ") & "at " & _
    FormatDateTime(Now() - dtStartTime, 3), vbInformation

The vlookup formula that I came up with through macro recording is:

ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[-1]C[-18]=""For Tracking"",R[-1]C[-18]=""For PRO# Confirmation""),IF(ISNA(VLOOKUP(R[-1]C[-18],'[PLS LTL Shipment Tracker 06152011.xlsx]MMDD'!C2,1,0)=TRUE),""Not Tracked"",""Tracked""),"""")"
    Range("S2").Select

Another aspect of the vlookup reference file (File2) is it's stored in monthly folders with the filename indicating the current date so it would be great if the macro automatically looks into the folder for the current month. The above formula in reference style is:

=IF(OR(R10="For Tracking",R10="For PRO# Confirmation"),IF(ISNA(VLOOKUP($A10,'\\Network\Staff_Documents\EmpName\CurrentMonth\[PLS LTL Shipment Tracker 06132011.xlsx]MMDD'!$B:$B,1,0)=TRUE),"Not Tracked","Tracked"),"")

I'm just discovering VBA so kindly bear with me. My existing code looks lengthy and repetitive to me and it's quite clear that I just slapped different codes together to achieve this. I've searched exhaustively and tried to customize bits & pieces of what I was able to look up to my problem but the solution still evades me.

Would greatly appreciate any help.