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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Where are you going to paste the rows ??
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top