title: How do I Speed up VBA Copy and Insert code
body: <p>Steps:</p>
  
  <p>-User-form is initialized asking for two inputs: New KPI and where to do add it, NewKPI.Value and CboKPI.Value respectively (Not a problem).</p>
  
  <p>-A new line is added in the reference based on the above. User is asked to input formulas into the reference line (Not a problem).</p>
  
  <p>-The code below is executed. After every CboKPI.Value a new line is inserted. The reference line is copied into it (with formulas and formatting). In addition, the information from the row before it is copied into it to remove the "Reference" values.</p>
  
  <p>In short, the code in step 3 is far too slow. I have about 2500 rows and the code takes more then <strong>25 min to run</strong> now. I am looking to speed up my code, with <strong>explanation</strong> please (so I can learn, I am new to coding on VBA). </p>
  
  <p>I have already tried the following, disabled: screen-updating, automatic calculation, events displaying page breaks while the code is executing. I also understand that inserting and copying codes in vba is slow (don't know why relative to other procedures).</p>
  
  <p>Thanks in advance. </p>
  
  <pre><code>Dim RowRef As Long, MSP As Worksheet, LastRowRef As Long, i As Long, NewRow As Long
  
  'Finds the row where the New KPI is
   RowRef = .Columns("I").Find(what:=NewKPI.Value, LookIn:=xlValues, 
   MatchCase:=True, LookAt:=xlWhole).Row
  
  'Finds where the Reference lines end
   LastRowRef = .Columns("B").Find(what:="Reference", LookIn:=xlValues, 
   SearchDirection:=xlPrevious, LookAt:=xlWhole).Row
  
   For i = .Cells(Rows.Count, "I").End(xlUp).Row To LastRowRef Step -1
  
  'Finds lines where the "Add After" KPI is situated
   If .Cells(i, "I").Value = CboKPI.Value Then
      NewRow = i + 1
  
      'Adds a new line after "Add After" Kpi
      Rows(RowRef).EntireRow.Copy
      Rows(NewRow &amp; ":" &amp; NewRow).Insert shift:=xlDown
  
      .Range(.Cells(NewRow, "B"), .Cells(NewRow, "G")).Value = .Range(.Cells(i, "B"), .Cells(i, "G")).Value
  
  End If
  Next i
  </code></pre>