Run time error 1004 for dynamic vlookup vba

[Starkers]

Hi All

I would like to create a dynamic vlookup across two workbooks; Book1 and Book2.

Book1 is the source file and Book2 is the file which I would like my values to be paste on.

The vlookup formula would be =VLOOKUP(K2,'C:\Users\Desktop\Mar22\[Book1.xlsx]Sheet3'!$E:$I,5,0)

However, the code below had produced an error:

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet


'Directory of source file
Set sourceBook = Workbooks.Open("C:\Users\Desktop\Mar22\Book1.xlsx")

'Worksheet names
Set sourceSheet = sourceBook.Worksheets("Sheet3")
Set outputSheet = ThisWorkbook.Worksheets("TopSegments")


With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row


   .Range("K2:K" & OutputLastRow).Formula = _
        "=VLOOKUP(F2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$E$2:$I$" & SourceLastRow & ",5,0)"


End With

End Sub

Run-time error '1004':
Application-defined or object-defined error

 

[Micron]

Have to run out for a while, but thought I'd suggest to check that the wb is not protected or already open. The latter can occur if it was opened in code in a prior attempt and not properly terminated. For that, check Task Manager before running the code. Note that you have to look in Processes as well as Applications list in TM.