title: Excel Vba: Creating loop that checks if the values in column A matches and copy all the rows to a new spreadsheet
body: <p>I need to select all the rows in column A that have the same the value and paste them to a new spreadsheet named with the copied name. </p>
  
  <p><img src="https://i.stack.imgur.com/odBQO.png" alt="enter image description here"></p>
  
  <p>In the example picture when I run macro and  input value Banana I should get all the rows that contain banana in column A.</p>
  
  <p>I found following vba code from the internet and tried to modify it to my needs but I'm stuck:</p>
  
  <pre><code>Sub LookForAllSameValues()
  
     Dim LSearchRow As Integer
     Dim LCopyToRow As Integer
  
     On Error GoTo Err_Execute
  
     'Start search in row 4
     LSearchRow = 2
  
     'Start copying data to row 2 in Sheet2 (row counter variable)
     LCopyToRow = 2
  
     Uname = InputBox("Test")
     ActiveWorkbook.Worksheets.Add.Name = Uname
  
     While Len(Range("A" &amp; CStr(LSearchRow)).Value) &gt; 0
  
        'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("A" &amp; CStr(LSearchRow)).Value = Uname 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(Uname).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>
  
  <p>This code almost works. It asks user to input string to search and then it creates a new worksheet named as this one. The problem lies in the loop, I debugged the code and for some reason it just skips copy paste loop</p>
  
  <p>How do I get the loop working?</p>
  
  <p>Output when the code is run:</p>
  
  <p><img src="https://i.stack.imgur.com/xzkSL.png" alt="enter image description here"></p>