Duplicate data sets multiple times in VBA

[hydromodller]

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!

[Kenneth Hobs]

Change x to the times you want it copied.

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

[hydromodller]

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

[SamT]

From Kenneth's example

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

[hydromodller]

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

[SamT]

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

[letter_V]

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

[SamT]

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.