title: Excel Macro, read a worksheet, select range of data, copy selection
body: <p>I need to write a macro that reads a worksheet of GeoTechnical data, selects the data based off a value in a particular row, select that row and continue reading until the end of worksheet.  Once all rows are selected, I then need to copy those rows into a new worksheet.  I haven't done VBA in about 10 years, so just trying to get back into things. </p>
  
  <p>For example, I want the macro to read the worksheet, when column "I" contains the word "Run" on a particular row, I want to then select from that row, A:AM. Continue reading through the worksheet until the end of it.  The end of the document is tricky as there are up to 10-15 blank rows sometimes in between groups of data in the worksheet.  If there is more then 25 blank rows, then the document would be at the end.  Once everything is selected, I then need to copy the selection for pasting into a new worksheet. Here is the code I have thus far, but I'm unable to get a selection:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Option Explicit
  Sub GeoTechDB()
        Dim x As String
        Dim BlankCount As Integer
        ' Select first line of data.
        Range("I2").Select
        ' Set search variable value and counter.
        x = "Run"
        BlankCount = 0
        ' Set Do loop to read cell value, increment or reset counter and stop loop at end    'document when there
        ' is more then 25 blank cells in column "I", copy final selection
        Do Until BlankCount &gt; 25
           ' Check active cell for search value "Run".
           If ActiveCell.Value = x Then
              'select the range of data when "Run" is found
              ActiveCell.Range("A:AM").Select
              'set counter to 0
              BlankCount = 0
              'Step down 1 row from present location
              ActiveCell.Offset(1, 0).Select
           Else
              'Step down 1 row from present location
              ActiveCell.Offset(1, 0).Select
              'if cell is empty then increment the counter
              BlankCount = BlankCount + 1
           End If
        Loop
     End Sub
  </code></pre>