PDA

View Full Version : Duplicate data sets multiple times in VBA



hydromodller
07-29-2013, 01:15 AM
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
07-29-2013, 08:41 AM
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
07-29-2013, 09:52 AM
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
07-29-2013, 10:18 AM
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
07-29-2013, 10:40 AM
Thanks Sam, that's a very neat little modification of the code.

SamT
07-29-2013, 11:05 AM
BTW, if X can ever be larger than 32K, change it from an Integer to a Long.

letter_V
10-29-2015, 08:05 PM
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
10-29-2015, 09:00 PM
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.