title: How to loop through a column to copy all matching rows to another sheet using vba?
body: <p>EDIT#1:
  I am trying to loop through a column to find a list of pre-specified numbers and if match is found, copy the entire row to another tab but I am getting error. Could some one please help?</p>
  <p>Based on the error that I was getting for my range, I felt that it was because of the way I was getting my samples range, so I changed it but I am still getting it.</p>
  <p><strong>Error:</strong></p>
  <p><strong>Runtime Error '1004':</strong></p>
  <p><strong>Method 'Range' of object '_Global' failed</strong></p>
  <p>I am getting this error for this line:</p>
  <pre><code>For Each c In Range(rng)
  </code></pre>
  <p>Here's the code:</p>
  <pre><code>Sub Sampler()
  Dim ws As Worksheet
  Dim cell As Range, c As Range, val As Variant
  Dim rng As Variant
  Dim answer As Long, lr As Long
  Dim ans As Variant
  Dim myrange As Variant
  Dim leadsheet As Variant
  
      answer = MsgBox(&quot;Does your population have serial number column starting from 1?&quot;, vbQuestion + vbYesNo + vbDefaultButton2, &quot;Very Important&quot;)
      
      If answer = vbYes Then
          MsgBox &quot;Great!&quot;
      Else
          MsgBox &quot;Press Esc. and add the serial number column in the population.&quot;
          Exit Sub
      End If
      
      ans = InputBox(&quot;Enter tab name for random data&quot;, &quot;Sample Selector&quot;)
      
      Worksheets(ans).Activate
      
      Set rng = Application.InputBox(&quot;Select a range for samples&quot;, &quot;Obtain Range Object&quot;, Type:=8)
  
      MsgBox &quot;The cells selected were &quot; &amp; rng.Address
      leadsheet = InputBox(&quot;Enter tab name for population&quot;, &quot;Population Tab Finder&quot;)
      
      Set ws = Worksheets(leadsheet)
      
      'check each cell in the specific worksheet if the criteria is matching
      For Each c In Range(rng)
          Set myrange = ws.Range(&quot;A1&quot;, ws.Range(&quot;A1&quot;).End(xlUp))
          For Each cell In myrange
              If cell.Value = c Then
                  lr = Sheets(ans).Range(&quot;A&quot; &amp; Rows.Count).End(xlDown).Row
                  cell.EntireRow.Copy Destination:=Sheets(ans).Range(&quot;S&quot; &amp; lr + 1)
              End If
          Next cell
      Next
  
  End Sub
  </code></pre>
  <p>Any and all help would be greatly appreciated.
  Thank you.</p>