Duplicate data sets multiple times in VBA

Hi all,
I have been battling with what seems like a very simple problem but it has beaten me so far!
I have data in columns in excel (207 rows) in a number of columns. I would like to write a piece of VBA which allows me to copy these 207 rows below the 207 rows above it multiple times, in this case 132 times. So in the end I will have the same 207 rows of data repeated 132 times down a column. If there is a way of varying the number of times it repeats that would also be very useful to me!

I have multiple columns I would to do this to but I guess a simple modification of the code would allow this.

Thanks in advance!

Change x to the times you want it copied.

Code:

---

Sub CopyColRowsX()
  Dim x As Integer, r As Range, r1 As Range
  x = 3
  With ActiveCell
    Set r = Range(Cells(1), Cells(Rows.Count, .Column).End(xlUp))
  End With
  With r
    Set r1 = .Cells(.Rows.Count + 1)
    .Copy r1.Resize(x * .Rows.Count)
  End With
  Cells(1).Select
End Sub

---

Thank you so much Kenneth, this is absolutely perfect! It will save me a lot of time creating data sets in my Msc project.

From Kenneth's example

Code:

---

Sub CopyColRowsX()
  Dim x As Integer, r1 As Range, Response As Integer
 
  Response = MsgBox("This Sub will replace all the contents below the slelction" _
  & Chr(13) & "with copies of the selection." _
  & Chr(13) & "Press Cancel  to quit.", vbOKCancel)
  If Response = vbCancel Then Exit Sub
 
  x = InputBox("Enter the number of times to copy the selection")
 
  With Selection
    Set r1 = Cells(.Rows.Count + 1, .Column)
    .Copy r1.Resize(x * .Rows.Count)
    .Cells(1).Select
  End With
End Sub

---

Thanks Sam, that's a very neat little modification of the code.

BTW, if X can ever be larger than 32K, change it from an Integer to a Long.

I know this is old but, this almost fits perfectly for what I need. The only problem is that I lose all my formated/merged cells when they are copied downward. What can be added to this that can keep the formatting of the copied cells?

Thanks

Nothing simple. Merged Cells don't play nice.

Instead of using Merged Cells, try un-merging them and using Format Cells >> Alignment >> Horizontal >>Center Across Selection. That looks identical to using Merged Cells on the Worksheet, but "Plays" a lot nicer.