title: Copy data from 2 different sheets and apply to another
body: <p>I wish to copy data from 2 different sheets based on a cell value in the third sheet and put these values in the 3rd sheet after applying vlookup via vba...</p>
  
  <p>I am using the following code to do so:</p>
  
  <pre><code>For TableRowCntr = 0 To i
          Range("A" &amp; 9 + TableRowCntr + n).Select
          ActiveCell.Value = "Fiber No." &amp; TableRowCntr + 1
          Selection.Font.Size = 8
          Range("A" &amp; 11 + TableRowCntr + n).Select
          ActiveCell.Value = "Individual Splice Loss"
          Selection.Font.Size = 8
          With Selection.Font
              .ColorIndex = 4
              .TintAndShade = 0
          End With
          FldrString = Null
          FIBERREPORTNAME = Null
          fiberreportname1 = Null
          Set appExcel = New Application
          Set appExcel1 = New Application
          'Set openWorkBook = ""
          'Set openWorkBook1 = ""
          For TableCOLCntr1 = 0 To j
              iTblCntr = Choose(TableCOLCntr1 + 1, "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC")
              iAlphabet = iTblCntr &amp; 9 + TableRowCntr + n
              Range(iAlphabet).Select
              FIBERREPORTNAME = Choose(TableRowCntr + 1, "A001", "A002", "A003", "A004", "A005", "A006", "A007", "A008", "A009", "A010", "A011", "A012", "A013", "A014", "A015", "A016", "A017", "A018", "A019", "A020", "A021", "A022", "A023", "A024", "A025", "A026", "A027", "A028", "A029", "A030", "A031", "A032", "A033", "A034", "A035", "A036", "A037", "A038", "A039", "A040", "A041", "A042", "A043", "A044", "A045", "A046", "A047", "A048", "A049", "A050", "A051", "A052", "A053", "A054", "A055", "A056")
  
  
              ' open workbook
              Set appExcel = New Application
              openWorkBookName = "C:\Users\Shikhar\Desktop\VTL Rewa\Ferozepur\Templates\Fiber Reports\Ferozepur to Jalallabad\A to B\" &amp; SheetName &amp; "\" &amp; FIBERREPORTNAME
              Set openWorkBook = appExcel.Workbooks.Open("C:\Users\Shikhar\Desktop\VTL Rewa\Ferozepur\Templates\Fiber Reports\Ferozepur to Jalallabad\A to B\" &amp; SheetName &amp; "\" &amp; FIBERREPORTNAME)
  
              'C:\Users\Shikhar\Desktop\VTL Rewa\Ferozepur\Templates\Fiber Reports\Ferozepur to Jalallabad\A to B\
  
  
  
              ActiveCell.Value = "=vlookup(" &amp; iTblCntr &amp; "6," &amp; FIBERREPORTNAME &amp; ".xls!C17:E" &amp; i + 17 &amp; ",3,TRUE)"
              Selection.Font.Size = 8
  
              openWorkBook.Close
              appExcel.Quit
              Set appExcel1 = New Application
              fiberreportname1 = Choose(TableRowCntr + 1, "A001", "A002", "A003", "A004", "A005", "A006", "A007", "A008", "A009", "A010", "A011", "A012", "A013", "A014", "A015", "A016", "A017", "A018", "A019", "A020", "A021", "A022", "A023", "A024", "A025", "A026", "A027", "A028", "A029", "A030", "A031", "A032", "A033", "A034", "A035", "A036", "A037", "A038", "A039", "A040", "A041", "A042", "A043", "A044", "A045", "A046", "A047", "A048", "A049", "A050", "A051", "A052", "A053", "A054", "A055", "A056")
              openWorkBookName1 = "C:\Users\Shikhar\Desktop\VTL Rewa\Ferozepur\Templates\Fiber Reports\Ferozepur to Jalallabad\B to A\" &amp; SheetName &amp; "\" &amp; fiberreportname1
              Set openWorkBook1 = appExcel1.Workbooks.Open("C:\Users\Shikhar\Desktop\VTL Rewa\Ferozepur\Templates\Fiber Reports\Ferozepur to Jalallabad\B to A\" &amp; SheetName &amp; "\" &amp; fiberreportname1)
  
              iAlphabet = iTblCntr &amp; 10 + TableRowCntr + n
              Range(iAlphabet).Select
  
  
              ActiveCell.Value = "=vlookup(" &amp; iTblCntr &amp; "6," &amp; fiberreportname1 &amp; ".xls!C17:E" &amp; i + 17 &amp; ",3,TRUE)"
              'ActiveCell.Value = "test 1"
              Selection.Font.Size = 8
              openWorkBook1.Close
              appExcel1.Quit
              iAlphabet = iTblCntr &amp; 11 + TableRowCntr + n
              Range(iAlphabet).Select
              Selection.Font.Size = 8
              With Selection.Font
                  .ColorIndex = 4
                  .TintAndShade = 0
              End With
              ActiveCell.Value = "=sum(" &amp; iTblCntr &amp; 9 + TableRowCntr + n &amp; ": " &amp; iTblCntr &amp; 10 + TableRowCntr + n &amp; ")/2"
          Next
          n = n + 2
      Next
  </code></pre>
  
  <p>The problem i am facing is once the code is executed i get the same formula in both the cells... of Row 9 and 10.... whereas the objective is to have data from workbook 1 in row 9 and data from workbook 2 in row 10.</p>
  
  <p>Kindly help.
  Please do let me know if you need help in understanding the code above.</p>