title: Excel VBA loop until blank cell and copy worksheet to new workbook
body: <p>I have a list of ID#s in column A on Sheet 2 (starting at cell A2).  </p>
  
  <p>I am trying to create a macro to loops through each ID #, copies it into cell A9 on Sheet 1 and then copies Sheet 3 into a new workbook. </p>
  
  <p>For each ID#, Sheet 3 should be copied into that same new workbook under a different worksheet/tab.  </p>
  
  <p>I am not a coder so all I have is what I can find on Google and I can't seem to get everything in order.  Any and all help is greatly appreciated. </p>
  
  <p>This is what I have so far.. what i cant figure out is how to end the loop at blank cell, how to get the macro to revert back to the source after copying worksheet to new workbook, and then how to add the subsequent loops to that now existing workbook.  </p>
  
  <pre><code>    Sub Test1()
    Dim x As Integer
    Application.ScreenUpdating = False
    ' Set numrows = number of rows of data.
    NumRows = Range("a2", Range("a2").End(xlDown)).Rows.Count
    ' Select cell a2.
    Range("a2").Select
    ' Establish "For" loop to loop "numrows" number of times.
    For x = 1 To NumRows
       Sheets("Sheet 1").Range("A9").Value = ActiveCell
        Sheets("Sheet 3").Copy
       ' Selects cell down 1 row from active cell.
       ActiveCell.Offset(1, 0).Select
    Next
    Application.ScreenUpdating = True
  </code></pre>
  
  <p>End Sub</p>