Vlookup with the latest File in folder

[LearnerDude1]

Hello I am trying to create VBA code where it extract data from the most recent folder in a directory and uses it to drop data to another folder. I got the latest file part working but i'm having trouble with the Vlookup portion i.e calling out the latest file.

Here is my latest file code

  Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date

    MyPath = "C:\Users\TAmon1\Desktop\OverSubscription Dash"
    If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop

And here is the Vlookup portion

Range("N2").Select
ActiveCell.FormulaR1C1 =VLOOKUP(C[-13],'Workbooks.Open MyPath & LatestFile'!C1:C11,11,FALSE)"

How can I call out the latest file properly

 

[sijpie]

Hi LearnnerDude,

please when posting code, use code tags around your code as shown in red below.

Don't 'select' cells or sheets or anything in your code unless you really must. (See my handy guide, link below) Just set the value or formula of the cell. Far easier and a lot, lot quicker

    Range("N2").Formula = "=VLOOKUP(A2,[" & Mypath & LatestFile & "]'Sheetname with input data'!C1:C11,11,False)"