title: excel macro that automatically creates a new sheet that continues a "for loop" from previous sheet1
body: <p>I have written a vba code that has a <code>"for next"</code> loop 1 to 10 and displays the result in respective rows in ColumnA, i want when it reaches row 5 the macro should automatically create a new sheet named "New sheet" and the <code>"for next"</code> continues from 6 til it reaches 10. I'm new to VBA and the code I wrote doesn't seem to work.</p>
  
  <p>My VBA code:</p>
  
  <pre><code>Sub test()
  
  Dim a As Long
  Dim WS As Worksheet
  
  Set WS = Worksheets.Add(After:=Worksheets(1))
  WS.Name = "New Sheet"
  
  n = 1
  col1 = 1
  
  For a = 1 To 10
  
      If n &lt;= 5 Then
  
          Cells(n, col1).Value = a
          n = n + 1
  
      End If
  
      If n &gt; 5 Then
  
          WS.Cells(n, 1).Value = a
          n = n + 1
  
      End If
  
  Next a
  
  
    End Sub
  </code></pre>