title: Copy data from closed workbook and paste if they data in common
body: <p>I am a trainee in a company and basically became tech support (better than coffee maker though)
  And so i'm trying to get one closed workbook to copy data into an open one
  So far this is the code i've got but i've some troubles with the if statement</p>
  
  <pre><code>Sub allergo()
      Dim lastRow As Long
      Dim bkBk1 As Workbook, wkBk2 As Workbook
      Dim wkSht As Object
      Dim mnt As String
      lr = wkBk2.Sheets(1).Range("R" &amp; Rows.Count).End(xlUp).Row
      mnt = InputBox("Enter Filename")
      Set wkBk1 = ActiveWorkbook
      Set wkBk2 = Workbooks.Open("Documents\" &amp; mnt &amp; ".xlsx")
  
      For Each cell In wkBk1.Sheets(1).Range(wkBk1.Sheets(1).Cells(2, "R"), wkBk1.Sheets(1).Cells(lr, "R"))
  
      wkBk1.Sheets(1).Range("R1:R" &amp; lr).Value = wkBk2.Sheets(1).Range("R1:R" &amp; lastRow).Value
  
      wkBk2.Close
  End Sub
  </code></pre>
  
  <p>Basically I've got two files that have the same columns but between some weeks rows disapear or appear as each row is a purchase order with a specific number and in row R we add comments and i want the comments in column R to copy to the new work book on the row where there's the same purchase order number. While some comments are attached to purchase orders that are not in the file anymore so they are not needed and some purchase orders have moved so i can't just copy paste.
  Not sure the If is a good idea as it won't compare my row E 146 with all the others of the new work book and thus only test E146 closed workbook = E146 newworkbook while it should test E146 closed = E147/E148 etc..</p>
  
  <p>This is the closed workbook:
  <a href="https://i.stack.imgur.com/o8jYa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o8jYa.png" alt="The closed workbook"></a></p>
  
  <p>This is the new workbook:
  <a href="https://i.stack.imgur.com/1webD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1webD.png" alt="The new workbook"></a></p>
  
  <p>Thanks for the help !</p>
  
  <p>Maybe a VLOOKUP is possible for this kind of things i'm not sure ..</p>
  
  <pre><code>Sub strilltrying()
  Dim ws As Worksheet
  Set ws = Sheets("Sheet 1")
  Dim lr As Long
  lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
  
  
  
  Cells(lr, "R").Formula = "=VLOOKUP(E2,'H:\Documents\[OPEN ORDERS 16.07.2018.xlsx]Sheet 1'!$E:$R,14)"
  
  
  End Sub
  </code></pre>
  
  <p>This is the vlookup formula that works but not fully as i've to take two different variables.. I found that index + match can do it but i'm having a hard time figuring it out.
  I'd need an index formula that compares column E and column J in the way of the vlookup.</p>