Excel macro slow with many sheets
<p>At work I use a VBA macro in Excel that for each row from a table, it needs to produce a single sheet with a specific format. </p>

<p>I have a working code that runs fine but the problem I'm getting is that each new sheet takes longer to get copied at a quadratic pace, which means that the whole process scales as the 3rd power of the amount of rows, which is not acceptable. </p>

<p>run time with different methods:</p>

<p><img src="https://i.stack.imgur.com/soXYI.png" alt="run time with different methods"></p>

<p>I've been timing different parts of the code and concluded that the problem is the timing increases as a function of the amount of sheets in the document (if I delete each new sheet before creating a new one, then there's no quadratic increase in time). Creating a new sheet and then copying from the Template sheet seems a little faster but is a marginal improvement and the resulting sheet is not properly formatted. </p>

<p>This is the relevant part of my code:</p>

<pre><code>Application.ScreenUpdating = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

For i = start To end
        ' Create new sheet from template
        Worksheets("Template").Copy After:=Worksheets(numsheets) 
        numsheets = numsheets + 1

        ' Change sheet's name
        item_index = Worksheets("Table").Cells(i, item).Value            
        name = "Item " &amp; item_index
        ActiveSheet().Name = name

        ' Insert data
        Worksheets(name).Range("I30").Value = i

    End If
Next i

Application.ScreenUpdating = True
Application.Calculation = CalcState
Application.EnableEvents = True
Application.DisplayStatusBar = True
</code></pre>

<p>Is there any way to optimise this so that I don't get this cubic increase in running time? </p>

<p>Thanks!</p>