title: Copy cells in a new sheet by separator VBA
body: <p>I have this problem in Excel that I want to solve using Macros in VBA. I have a sheet that contains data in this format: </p>
  
  <pre><code>separator
  1
  2
  6
  3
  8
  342
  532
  separator
  72
  28
  10
  21
  separator
  38
  23
  234
  </code></pre>
  
  <p>What I want to do is to create a VBA macro that creates a new sheet for every series of data (a series starts from the "separator" and ends before the next one or at the end of the initial sheet) and copy respective data in the new sheets.
  Example:</p>
  
  <pre><code>1
  2
  6
  3
  8
  342
  532
  </code></pre>
  
  <p>in sheet1</p>
  
  <pre><code>72
  28
  10
  21 
  </code></pre>
  
  <p>in sheet2 etc.
  Thank you very much, I appreciate it!
  This copies data from beginning to the first separator ("q"):</p>
  
  <pre><code>Sub macro1()
  Dim x As Integer
  x = 1
  
  Sheets.Add.Name = "Sheet2"
  
  'Get cells until first q
  
  Do Until Sheets("Sheet1").Range("A" &amp; x).Value = "q"
  Sheets("Sheet2").Range("A" &amp; x).Value = Sheets("Sheet1").Range("A" &amp; x).Value
  x = x + 1
  Loop
  
  
  End Sub
  </code></pre>