Need to paste multiple rows in Excel multiple times

[litifeta]

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.

 

[Michael M]

Where are you going to paste the rows ??

 

[litifeta]

Where are you going to paste the rows ??

Aha ... good point. It could be on a fresh worksheet, or below the 3 rows I am copying from.

 

[Michael M]

OK, this will post below the existing data

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]

OK, this will post below the existing data

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

 

[Michael M]

Try

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]

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