title: How to use VLOOKUP and get details from other sheet using VBA
body: <p>I have following sheet available in my workbook</p>
  
  <pre><code>test,input,model,cm,mm,output
  </code></pre>
  
  <p>I would like to get input from input sheet.</p>
  
  <ol>
  <li>add the column before the test column which is "mark" and vlookup based on house(,) to sheet "CM" house to mark.</li>
  <li>I need to get "dear" detail from  "CM" based house</li>
  <li>I need to get "son" detail from  "MM" based house</li>
  <li>I need to add column between brother and son. Which has named as mark and vlook based on son and get from model sheet.</li>
  </ol>
  
  <p>Input                                               </p>
  
  <pre><code>test    mail    god house   dear    moon    son brother loosee  man boy girl    test
  dd  d   d   sd  dfd 123 dfd ad  d   df  sd  d   d
  
  
  model                                               
  pop mark                                            
  123 jklm                                            
  CM                                              
  house   dear    mark                                        
  sd  dfd love                                        
  MM                                              
  
  moon    son                                         
  123 dfd                                         
  </code></pre>
  
  <p>I am trying to write the following code to get all details and get output. but I am getting failed pls guide me to solve it.</p>
  
  <pre><code>Option Explicit
  
  Sub CopyRows()
      Dim ws1 As Worksheet, ws2 As Worksheet
      Dim i As Integer, k As Integer
      Dim ws1LR As Long, ws2LR As Long
  
      Set ws1 = Sheets("input")
      Set ws2 = Sheets("output")
  
      ws1LR = ws1.Range("A" &amp; Rows.Count).End(xlUp).Row + 1
      ws2LR = ws2.Range("A" &amp; Rows.Count).End(xlUp).Row + 1
  
      i = 2
      k = ws2LR
      Do Until i = ws1LR
          With ws1
              .Range(.Cells(i, 1), .Cells(i, 18)).Copy
          End With
  
          With ws2
              .Cells(k, 1).PasteSpecial
              .Cells(k, 1).Offset(1, 0).PasteSpecial
          End With
  
          k = k + 2
          i = i + 1
      Loop
  End Sub
  </code></pre>