title: Excel VBA Search for a value in a column and copy row to new sheet for all matching values
body: <p>What it is supposed to do is compare a name from column G, find that name in column E and if there is a match, copy all to a new sheet. Right now the code searches for a value called Mail box, each time it finds it in range of the column E it copies the whole row to a new sheet2. </p>
  
  <p>I am capable of transforming the code that instead of searching for Mail box it searches for a value in a cell G1, yet I would need the code to do it several times again for a value in row G2, G3, G4, G5...G20. </p>
  
  <pre><code>Sub SearchForString()
  
     Dim LSearchRow As Integer
     Dim LCopyToRow As Integer
  
     On Error GoTo Err_Execute
  
     'Start search in row 4
     LSearchRow = 4
  
     'Start copying data to row 2 in Sheet2 (row counter variable)
     LCopyToRow = 2
  
     While Len(Range("A" &amp; CStr(LSearchRow)).Value) &gt; 0
  
        'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("E" &amp; CStr(LSearchRow)).Value = "Mail Box" Then
  
           'Select row in Sheet1 to copy
           Rows(CStr(LSearchRow) &amp; ":" &amp; CStr(LSearchRow)).Select
           Selection.Copy
  
           'Paste row into Sheet2 in next row
           Sheets("Sheet2").Select
           Rows(CStr(LCopyToRow) &amp; ":" &amp; CStr(LCopyToRow)).Select
           ActiveSheet.Paste
  
           'Move counter to next row
           LCopyToRow = LCopyToRow + 1
  
           'Go back to Sheet1 to continue searching
           Sheets("Sheet1").Select
  
        End If
  
        LSearchRow = LSearchRow + 1
  
     Wend
  
     'Position on cell A3
     Application.CutCopyMode = False
     Range("A3").Select
  
     MsgBox "All matching data has been copied."
  
     Exit Sub
  
  Err_Execute:
     MsgBox "An error occurred."
  
  End Sub
  </code></pre>