title: Excel VBA copy range to a new sheet after 1,048,576 rows
body: <p>So I wrote a fairly simple Macro in VBA that updates a set of variables, then copying and pasting the updated values into a new sheet. The problem is that the volume is getting a bit overwhelming now, thus reaching the 1,048,576 row limit in Excel, causing the code to crash.</p>
  
  <p>I would like to update it so that whenever the rows limitation is reached, the script begins copying the cells to a new sheet (say, "FinalFile2","FinalFile3", etc) until it's fully executed.</p>
  
  <pre><code>Sub KW()
  '
  ' Exact KWs
  '
  Dim i, j, LastRow As Long
  Dim relativePath As String
  
  i = 2
  j = 2
  
  'LastRowValue'
  Sheets("Output").Select
  LastRow = Rows(Rows.Count).End(xlUp).Row - 1
  
  'Clean final output'
    Sheets("FinalFile").Select
      Range("A2").Select
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.ClearContents
      Range("A1").Select
  
  'Set Variables in Variables sheet'
  
  Do
  
  'Var 1'
      Sheets("Names").Select
      Range("A" &amp; i).Select
      Selection.Copy
      Sheets("Variables").Select
      Range("A2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  'Var 2'
      Sheets("Names").Select
      Range("B" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("B2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
   'Var 3'
      Sheets("Names").Select
      Range("C" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("C2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
   'Var 4'
      Sheets("Names").Select
      Range("D" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("D2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
   'Var 5'
      Sheets("Names").Select
      Range("E" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("E2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
    'Var 6'
      Sheets("Names").Select
      Range("F" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("F2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
     'Var 7'
      Sheets("Names").Select
      Range("G" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("G2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
      'Var 8'
      Sheets("Names").Select
      Range("H" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("H2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
  
       'Var 9'
      Sheets("Names").Select
      Range("I" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("I2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
  
       'Var 10'
      Sheets("Names").Select
      Range("J" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("J2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
  
       'Var 11'
      Sheets("Names").Select
      Range("K" &amp; i).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Variables").Select
      Range("K2").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  
  'Copy and Paste'
  
      Sheets("Output").Select
      Range("A2:AP2").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Sheets("FinalFile").Select
      Range("A" &amp; j).Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  'update counters'
  
  i = i + 1
  j = j + LastRow
  
  'end of loop condition'
  
  Sheets("Names").Select
  
  Loop Until IsEmpty(Cells(i, 1))
  
  
  
  End Sub
  </code></pre>