Need to paste multiple rows in Excel multiple times

litifeta

New Member
Joined
Mar 12, 2006
Messages
4
I am looking for a macro perhaps. I have 3 rows of data Columns C to G I want to paste multiple times. Perhaps thousands of times. Is there a way I can cope C1:G3 and then paste it a number of times? I would like in some cases this to be 300 times, and in other cases 1500 times.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Where are you going to paste the rows ??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
OK, this will post below the existing data

Code:
Sub MM1()
  Dim x As Integer, r1 As Range
   x = InputBox("Enter the number of times to copy the range")
  With Range("C1:G3")
    Set r1 = Cells(.Rows.Count + 1, .Column)
    .Copy r1.Resize(x * .Rows.Count)
    .Cells(1).Select
   End With
End Sub
 

litifeta

New Member
Joined
Mar 12, 2006
Messages
4

ADVERTISEMENT

OK, this will post below the existing data

Code:
Sub MM1()
  Dim x As Integer, r1 As Range
   x = InputBox("Enter the number of times to copy the range")
  With Range("C1:G3")
    Set r1 = Cells(.Rows.Count + 1, .Column)
    .Copy r1.Resize(x * .Rows.Count)
    .Cells(1).Select
   End With
End Sub

thanks. That worked a treat. Is there also a way I can repeat a stack of single rows three times. I have many worksheets with cost center number in Col A and cost center names in Col B. I want each row to be repeated twice ... (i.e) (each spreadsheet has different information and differing amounts. But each row must be 3 times)

Was

A B
349 Engineering assets
220 Finance
235 HR direct costs

NEW

A B
349 Engineering assets
349 Engineering assets
349 Engineering assets
220 Finance
220 Finance
220 Finance
235 HR direct costs
235 HR direct costs
235 HR direct costs
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Try

Code:
Sub MM1()
Dim r As Long, n As Integer
n = InputBox("How many rows")
    For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        Rows(r).Copy
        Rows(r).Resize(n).Insert
    Next r
End Sub
 

melendezml

Board Regular
Joined
May 5, 2014
Messages
63
I have a similar situation. I have 56 criteria that I must check to see if each person has met. Each person has a unique ID number. So I want to copy it 56 times and then later vlookup whether each criteria had been met

In one tab I have all ID numbers which can range up to 4,000 unique ID numbers.
And I want to copy down the ID number and the 56 criteria as many times as needed.

How can I do this?


Example tab 1:
ID 1
ID 2
ID 3

Tab 2:
Column A Column B
Criteria 1 ID 1
Criteria 2 ID 1
....
Criteria 56 ID 1
Criteria 1 ID 2
....
Criteria 56 ID 2
...
Criteria 1 ID 2
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top