Q:

VBA - lookup in variable workbook

I have a little bit of a problem within an Excel Macro
I have a file populated with huge amount of data extracted from different software and that has to be updated on a monthly basis.
There is data from A2 to O & Lastrow
in column L there is a code that I use to split the sheet into as many sheets as needed (one per existing codes)
Lets say my sheet has 50 lines, the codes present in L are 0250, 40 times and 1225, 10 times so I'll have 2 new sheets
Then all these sheets are saved as individual files for further treatment - and column N of the newly created file will be changed by human
So excel will create a folder and save these 2 sheets as 2 xlsx files according to the month we're in
In my example, it will save them as:
c:\ACCOUNTS\SEP\0250 SEP 19.xlsx
c:\ACCOUNTS\SEP\1225 SEP 19.xlsx
I'm looking for either in the raw extract entirely or at the time the file is split to add in column O a vlookup that will analyze each of these files from the previous month
So I'd like to have a piece of code that will input a Vlookup in the cells, depending of what's in column L of the sheet name to have
=vlookup(A2,c:\ACCOUNTS\AUG\0250 AUG 19.xlsx!A2:O & lastrow,14,0)

or
=vlookup(A2,c:\ACCOUNTS\AUG\1225 AUG 19.xlsx!A2:O & lastrow,14,0)

Sub SPLIT()

Dim main As Worksheet
Dim lastrow As Long

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String

Set xWb = Application.ThisWorkbook

mBefore = Format(DateAdd("m", -1, Date), "MMM yy")
mPrevious = Format(DateAdd("m", -1, Date), "MMM yy")
Set main = Sheets("Global")
main.Activate

'ActiveSheet.name = "Global"
'Set main = Sheets("Global")

'Calculation of last line
lastrow = main.Cells(Rows.Count, "l").End(xlUp).Row
'MsgBox lastrow

Set FDWDownload1 = Sheets("Global")

Sheets.Add(before:=Sheets("Global")).name = "List"

Set Ref = Sheets("List")

'Creates the table
FDWDownload1.Range("L" & StartPointRow + 1 & ":L" & lastrow).Copy
        Ref.Activate
        Ref.Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Range("B:B").RemoveDuplicates Columns:=1
        Application.CutCopyMode = False

        RefLastRow = Ref.Cells(Rows.Count, "B").End(xlUp).Row
        FDWDownload1.Activate

If RefLastRow > 0 Then
            'For the second row to the end of the table on the References tab
            For i = 3 To RefLastRow
                'Copy the header from the download
                FDWDownload1.Range("A1:P1").Copy

            'Add a new sheet placed after the sheet "Global" & name it according to the reference table
                Sheets.Add(after:=Sheets("Global")).name = Ref.Range("B" & i)
                Set ws = Application.ActiveSheet

                'Paste the header into each newly created sheet
                ws.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                ws.Range("A1").PasteSpecial Paste:=xlPasteFormats

                  'Get the number of the last row on the newly created sheet
                ForLastRow = ws.Cells(Rows.Count, "L").End(xlUp).Row

                'As the download is grouped in Product Line order find the first and last row of the Product according to the worksheet name
                    With FDWDownload1
                        FDWDownload1.Activate
                        k = .Range("L:L").Find(what:=ws.name, after:=.Range("L1")).Row
                        l = .Range("L:L").Find(what:=ws.name, after:=.Range("L1"), searchdirection:=xlPrevious).Row
                    End With
                Range("A" & k & ":O" & l).Copy
                ws.Activate
                ws.Range("A" & ForLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Columns("A:P").EntireColumn.AutoFit
                Columns("A:O").AutoFilter
                Range("A:O").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes

                ActiveSheet.name = ws.name & " " & "GRIR" & " " & mBefore

Next i
        Else
        End If

End Sub

A:

Has the solution to be in VBA? If not you should definetly check out PowerQuery which is included in Excel 2016 and higher. Power Query can do exactly what you asked and doesn't need any code.
Anyway, if I understand you correctly you need to set a formula with VBA.
Try this:
Sub set_formula()

lastrow = 10
Path = "C:\Folder\"
workbookname = "Template.xlsx"
sheetname = "Ids"

Range("D13").Formula = "=VLOOKUP(A2,'" & Path & "[" & workbookname & "]" & sheetname & "'!A2:O" & lastrow & ",14,0)"

End Sub

