title: Match and copy cell in worksheet to corresponding tab
body: <p>I receive a form like LTest from every sales order, I would like to copy certain data from LTest and copy it into 2016 Test 1. Each form is formatted the same and I want to use the data in cell B3 of LTest to select to proper tab in 2016 Test 1 and then insert the information in the corresponding cells of that same worksheet. The name of LTest will vary from one order to another and I will have to tweak the form to include an order number that will also be unique. </p>
  
  <p>One issues is LTest and 2016 Test 1 are different spreadsheets.</p>
  
  <pre><code>Sub Keysha_Bee()
  Dim wb1 As Workbook
  Dim ws1 As Worksheet
  Dim wb2 As Workbook
  Dim ws2 As Worksheet
  Dim SheetID As String
  Dim i As Integer
  Dim lrow As Integer
  
  Set wb1 = Workbooks("LTest")
  Set ws1 = wb1.Sheets(1)
  Set wb2 = Workbooks("2016 Test1")
  
  If InStr(ws1.Range("B3"), "FPPI") &gt; 0 Then SheetID = "FPPI-Routed"
  If InStr(ws1.Range("B3"), "USPPI") &gt; 0 Then SheetID = "USPPI-Routed"
  If InStr(ws1.Range("B3"), "Standard") &gt; 0 Then SheetID = "Standard"
  
  i = 1
  
  Do Until i &gt; wb2.Sheets.Count
      If wb2.Sheets(i).Name = SheetID Then Set ws2 = wb2.Sheets(i) Else GoTo Nexti
      lrow = ws2.Cells(Rows.Count, 2).End(xlUp).Row + 1
      ws2.Cells(lrow, 2) = ws1.Range("D6") 'Customer Name
      If ws2.Range("D14") = "" Then
          ws2.Cells(lrow, 3) = ws1.Range("D17") 'Agent's Name
          ws2.Cells(lrow, 4) = ws1.Range("D18") 'Auth Agent's Email
      Else
          ws2.Cells(lrow, 3) = ws1.Range("D15") 'Agent's Name
          ws2.Cells(lrow, 4) = ws1.Range("D16") 'Auth Agent's Email
      End If
      ws2.Cells(lrow, 5) = "NO" 'Routed, not sure what this is supposed to reference
      ws2.Cells(lrow, 6) = ws1.Range("D20") ' Routed
      ws2.Cells(lrow, 7) = ws1.Range("D26") ' Origin
      ws2.Cells(lrow, 8) = ws1.Range("D27") ' Hazardous
      ws2.Cells(lrow, 9) = ws1.Range("D28") ' UC Type
      ws2.Cells(lrow, 10) = "Date" 'Not sure what this is supposed to refference
  
  Nexti:
  i = i + 1
  Loop
  
  
  End Sub
  </code></pre>