title: use macro to copy a row into another worksheet if it meets a criteria
body: <p>I have a sheet in excel as below</p>
  
  <pre><code>1   apple   8/1/2013    8/2/2013    99373        11
  2   apple   8/13/2013   8/3/2013    2626282    2121
  3   berry   8/12/2013   8/4/2013    1289127     123
  4   berry   8/15/2013   8/5/2013    12712671   1234
  5   cherry  8/19/2013   8/6/2013    127127     3354
      apple   9/1/2013    9/5/2013    123456      200
      apple   9/2/2013    9/6/2013    246810      300
      berry   9/3/2013    9/7/2013    3691215     400
      berry   9/4/2013    9/8/2013    48121620    500
      cherry  9/5/2013    9/9/2013    510152025   600
      cherry  9/6/2013    9/10/2013   612182430   700
      apple   9/7/2013    9/11/2013   714212835   800
      berry   9/8/2013    9/12/2013   816243240   900
      berry   9/9/2013    9/13/2013   918273645   1000
      apple   9/10/2013   9/14/2013   10203040    1100
  </code></pre>
  
  <p>I am trying to get excel to copy every row that says either apple, berry, or cherry for the previous month (in this case - 9/1/13 - 9/30/13) and put them in the corresponding sheets. The sheet names are apple, berry and cherry. It then inserts a row and adds the row to the row after the last row with data. I also need it to generate the next number in the series in column a before copying it to the corresponding sheet. So row 6 would have the number 6 in column a.</p>
  
  <p>I have tried some VBA code but I am having some trouble making it dynamic to search all the sheet names rather than just "apple". Also I am having trouble finding the last row with data. Please see below my code:</p>
  
  <pre><code>Sub testIt()
  Dim r As Long, endRow As Long, pasteRowIndex As Long
  
  endRow = 15
  pasteRowIndex = 1
  
  For r = 1 To endRow
  
      If Cells(r, Columns("B").Column).Value = "apple" Then
  
  
          Rows(r).Select
          Selection.Copy
  
  
          Sheets("apple").Select
          Rows(pasteRowIndex).Select
          ActiveSheet.Paste
  
  
          pasteRowIndex = pasteRowIndex + 1
  
  
  
          Sheets("Sheet1").Select
      End If
  Next r
  End Sub
  </code></pre>