Inserting range to another sheet as rows multiple times
NickName:pavel svoboda Ask DateTime:2021-06-23T17:51:44

Inserting range to another sheet as rows multiple times

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.

Copyright Notice:Content Author:「pavel svoboda」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/68097508/inserting-range-to-another-sheet-as-rows-multiple-times

More about “Inserting range to another sheet as rows multiple times” related questions

Inserting range to another sheet as rows multiple times

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...

Show Detail

Copy Rows to Another Sheet Based on Date Range Multiple Times

I have an Excel workbook that has ~15 sheets. I'm looking for a way to copy rows to a new sheet based on the date range in column K. Example: Sheet 1: Date range (1/1/15 - 1/1/18) -> Copy all rows

Show Detail

Excel - Copying a Range (multiple Rows) and pasting and end of another sheet

I need to copy a range sheet1 ("Assembly1") and paste it after the last row of sheet2. I can find and identify the last row. I can copy and paste a single row. But I cant figure out multi...

Show Detail

Duplicating rows in another sheet

Here's my problem, I am trying to figure out how to make a button that will generate cells on another sheet using a number in a cell to set the number of duplicates. So on my first sheet I use a f...

Show Detail

Copy cell inot another sheet and Autofill the copied cell 10 times

I need to copy the cell contents in C10 from one sheet (called "New Customers") to another sheet's (called "Inventory") next available row. Once the cell is copied, it should be copied or autofil...

Show Detail

Copy a range with rows to another sheet

My requirement is to copy rows from sheet3 having font color black to sheet1.I have a range of rows selected from sheet3 in a workbook. I want to copy this and paste in sheet1.Selection part is ok,...

Show Detail

Copying range from one sheet to another and paste multiple times till it loops to next sheet

I am trying to copy cells A3, B4 and C2 from multiple sheets to paste in master sheet Range A:C. and cells A8 to C25 from multiple sheets to master sheet range D:F. The code I have copies all the...

Show Detail

Excel loop copy a range in sheet 1 and paste to sheet 2

I'm not sure how the logic of loop works. I have a table at sheet 1 with 105 rows and 120 columns. I want to do a loop, start with cell J6, copy a range of 100 rows and 16 columns. And transpose and

Show Detail

Find a range of multiple cells in another sheet

I am trying to enhance my current script. Sheet1 and Sheet2 contain only filepath names in column A. If a filepath in Sheet2 isn't found in Sheet1, it is copied over to sheet 3. 'row counter x = 1 '

Show Detail

Macro to Protect Sheet With Allow Inserting of Rows

I have prepared the below macro to unprotect a sheet, sort the data, then protect the sheet again. It works perfectly except, in my original protection setting I allowed users to insert rows, but ...

Show Detail