title: Copy a row in excel if it matches a specific criteria into a new worksheet
body: <p>I'm a novice to VBA and having issues in copying rows in one sheet to another based on certain criteria.</p>
  
  <p>I have tried searching for an answer in this forum, tried modifying the code to suit my requirement but been unsuccessful. Please help me.</p>
  
  <ol>
  <li>I need to search for no.s in column A of a worksheet. The search should start with No. 1, then 2, then 3 and so on.  </li>
  <li>Whenever "1" is found, copy the entire row to "Sheet 1".  </li>
  <li>After completing search for "1", start for "2". When a match is found, copy entire row to "Sheet 2".</li>
  <li>Similarly No. "3" and so on. </li>
  <li>Repeat this search for other no.s till end of column A.</li>
  </ol>
  
  <p>I have tried the following code:
  Note: i will vary from 1 to a pre-determined value.</p>
  
  <pre><code>Dim wb1 As Workbook, wb2 As Workbook
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim copyFrom As Range
  Dim lRow As Long '&lt;~~ Not Integer. Might give you error in higher versions of excel
  Dim strSearch As Integer
  
  Set wb1 = ActiveWorkbook
  Set ws1 = wb1.Worksheets("Burn Down")
  strSearch = "1"
  
  With ws1
  
      '~~&gt; Remove any filters
      .AutoFilterMode = False
  
      '~~&gt; I am assuming that the names are in Col A
      '~~&gt; if not then change A below to whatever column letter
      lRow = .Range("A" &amp; .Rows.Count).End(xlUp).Row
  
      With .Range("A3:A" &amp; lRow)
          .AutoFilter Field:=1, Criteria1:="=*" &amp; strSearch &amp; "*"
          Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
      End With
  
      '~~&gt; Remove any filters
      .AutoFilterMode = False
  End With
  
  '~~&gt; Destination File
  Set wb2 = Application.Workbooks.Open("C:\CSVimport\Sample.xlsx")
  Set ws2 = wb2.Worksheets("Sheet" &amp; i)
  
  With ws2
      If Application.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
          lRow = .Cells.Find(What:="*", _
                        After:=.Range("A3"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
      Else
          lRow = 1
      End If
  
      copyFrom.Copy .Rows(lRow)
  End With
  
  wb2.Save
  wb2.Close
  </code></pre>