Inserting range to another sheet as rows multiple times

Question

I am trying to copy range from sample reference card into another sheet and insert this range as row on above existing ones, multiple times.

Right now I have code that is working for inserting this range once:

Sub Nova_karta_export()

    Sheets("nova_karta").Select
    Rows("3:42").Select
    Selection.Copy
    Sheets("Export").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown

End Sub

I was able to modify it to insert this range multiple times based on value in cell "V1" in export sheet. But running this version is quite heavy on computing.

Sub Nova_karta_export_X_krat()

    Dim numberoftimestorun As Integer
    Dim i As Integer
 
    numberoftimestorun = Sheets("Export").Range("V1").Value
    
    For i = 1 To numberoftimestorun
        
    Sheets("nova_karta").Select
    Rows("3:42").Select
    Selection.Copy
      
    Sheets("Export").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    
    Next i
        
End Sub

If I insert range for example ten times is computes very slow. I need to add even 50+ ranges.

How can I speed up this process? Through variables? And how to insert number of repeats through message window? I want that user can write number of repeat into window after running macro and not to be dependent on cell "V1"

Thanks for help.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jun 23 '21 at 09:52

Christofer Weber · Accepted Answer · 2021-06-23T10:32:51.977

I have a similar macro in a file, and it uses resize, something like so:

Dim numberoftimestorun As Long
Dim i As Long
Dim source As Range

numberoftimestorun = Sheets("Export").Range("V1").Value
set source = Sheets("nova_karta").Rows("3:42")
source.Copy
Sheets("Export").Rows("3:3").Resize(numberoftimestorun * source.Rows.Count).Insert Shift:=xlDown

See if something like that would speed things up.
The code I borrowed from only used a single row as source, so I hope this works, untested.

Inserting range to another sheet as rows multiple times

1 Answers1