PDA

View Full Version : Help to create repeat macro until blank cell found



nbarttelot
02-19-2015, 06:17 AM
I have data in an unknown number of rows
I need to copy each row and paste it the number of times stated in in a corresponding cell.
I have got this to work for an individual row but cannot get it to loop down the rows until it reaches a blank cell.
This is the code I have to copy one row A2 contains the number of times to be repeated and should also be what is checked for being blank.


Sub Copy_Paste_2()
Dim I As Long
For I = 1 To Range("A2")
Range("B2:F2").Copy
Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next I
End Sub


This is my failed attempt at looping



Sub FindEmptyCell_1()
Range("A2").Select
Do
For I = 1 To Range("A2")
Range("B2:F2").Copy
Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next I
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I really appreciate any help/advice given, am about to repeat the some macro 50 times and then link them together :/

Bob Phillips
02-19-2015, 06:58 AM
Sub Copy_Paste_2()
Dim numrows As Long
Dim I As Long

numrows = .Range("B2").End(xlDown).Row - 1

For I = 1 To Range("A2")

Range("B2:F2").Resize(numrows).Copy
Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next I
End Sub

nbarttelot
02-19-2015, 08:20 AM
Thanks for replying, but it doesn't seem to work.
It copies the rows but it does not repeat the rows for the value of the cell in column A of each row.

Bob Phillips
02-19-2015, 09:11 AM
How many times does it do it?

nbarttelot
02-19-2015, 09:23 AM
It just copies each row once.

Bob Phillips
02-19-2015, 09:38 AM
And what is in A2?

nbarttelot
02-19-2015, 09:46 AM
A2 contains the number of times B2:F2 need to be repeated, A3 contains the number of times B3:F3 need to be repeated and so on

Bob Phillips
02-19-2015, 11:37 AM
Yes, but is the value 1 or something else.

nbarttelot
02-20-2015, 02:08 AM
A2 = 1 but this number can be different for each row