title: VLOOLUP - MACRO sheet different from source and destination
body: <p>Good day
  I have a sheet called "MACRO" that runs several macros using predefined workbooks as source and destination.</p>
  
  <p>In this particular requirement, I want to run a macro in my macro sheet, which will use a vlookup on "book2.xslx" with lookup cell, looks up values in "book3.xlsx" and updates "book2.xlsx" with the results</p>
  
  <p>All the macros out there run the vlookup from the active sheet, however i couldnt find anything to do the above.</p>
  
  <p>Please help :)</p>
  
  <p>Thanks</p>
  
  <p>To be more precise i am trying to do the following:</p>
  
  <p>Open "book2.xlsx", go to column "b" "cell 2" and insert the following formula 
  "=VLOOKUP(A2,[Book3.xlsx]Sheet1!$A:$B,2,0)"
  The same formula to be used all the way till "A the lookup cell" becomes blank", while the value in "A2" should change everytime it moves to the cell below</p>
  
  <p>Can you please help? </p>
  
  <p>Edited: Code im trying to run:</p>
  
  <pre><code>Sub VLOOKUP_DEPT()
  
  Dim wbk1 As Workbook
  
  strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"
  
  Set wbk1 = Workbooks.Open(strFirstFile)
  
   With wbk1.Sheets("sheet1")
  
  Range("B2") = Application.WorksheetFunction.vlookup(Range("A2"), Workbooks("C:\Users\hayekn\Desktop\book3.xlsx").Sheets("sheet1").Range("A:B"), 2, 0)
  
  End With
  
  
  wbk1.Close True
  
  MsgBox ("VLOOLUP-DEPT Completed!!")
  
  End Sub
  </code></pre>
  
  <p>I have also tried this, it runs through but does not return anything in "book2"</p>
  
  <pre><code>Sub VLOOKUP_DEPT()
  
  Dim wbk1 As Workbook
  
  strFirstFile = "C:\Users\hayekn\Desktop\book2.xlsx"
  strSecondFile = "C:\Users\hayekn\Desktop\book3.xlsx"
  
  Set wbk1 = Workbooks.Open(strFirstFile)
  Set wbk2 = Workbooks.Open(strSecondFile)
  
  Set Rng = wbk1.Sheets("Sheet1").Range("B2")
  Set Rng2 = wbk1.Sheets("Sheet1").Range("A2")
  Set Rng3 = wbk2.Sheets("sheet1").Range("A:B")
  
  Rng = Application.vlookup(Rng2, Rng3, 2, 0)
  
  wbk1.Close True
  wbk2.Close True
  
  MsgBox ("VLOOLUP-DEPT Completed!!")
  
  End Sub
  </code></pre>
  
  <p>Thanks</p>