Ref workbook by path and name - VBA

I think you need to use the Open function first:

Workbooks.Open Filename:="C:\Users\user1\Desktop\Book1.xlsx"
Set LookupWB = Application.Workbooks("Book1.xlsx")