Duplicate rows a given number of times in Excel?
 

I have a speard sheet that I recieved from a client for some vouchers we are
sending out to some of thier retail locations. It looks like this:

# of vouchers, dollar value, shipping address
5 100 123 street
30 50 456 ave
etc....

There a thousands of row and they need to be submitted as a batch file in
our system, however the batch system needs each voucher to be it's own row,
it cant read the "# of vouchers" and create that many, so for example the
first row is 5 vouchers it needs the dollar amount and address to be
displayed 5 times in seprate rows. How do I make excel look at the first
cell of each row and duplicate the row the number given in the fisrt cell?
I need excel to also recognize it has duplicated the row X number of times
and not to duplicate the duplicates.

Thanks,

Duplicate rows a given number of times in Excel?
 

Sub CreateBatchList()
Dim cnt As Long
Dim cnt2 As Long
For cnt = Range("A" & Rows.Count).End(xlUp).Row to 2 Step -1
'Assumes Header Row
For cnt 2 = 1 to Range("A" & cnt) - 1
Range("A" & cnt) = 1
Rows(cnt).Copy
Rows(cnt + 1).Insert Shift:=xlDown
Next
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"SB" wrote:

I have a speard sheet that I recieved from a client for some vouchers we are
sending out to some of thier retail locations. It looks like this:

# of vouchers, dollar value, shipping address
5 100 123 street
30 50 456 ave
etc....

There a thousands of row and they need to be submitted as a batch file in
our system, however the batch system needs each voucher to be it's own row,
it cant read the "# of vouchers" and create that many, so for example the
first row is 5 vouchers it needs the dollar amount and address to be
displayed 5 times in seprate rows. How do I make excel look at the first
cell of each row and duplicate the row the number given in the fisrt cell?
I need excel to also recognize it has duplicated the row X number of times
and not to duplicate the duplicates.

Thanks,

Duplicate rows a given number of times in Excel?
 

Thanks Charles, that did exactly what I was looking for, you just saved me a
hours of tedious work. Thank you so much!




"Charles Chickering" wrote:

Sub CreateBatchList()
Dim cnt As Long
Dim cnt2 As Long
For cnt = Range("A" & Rows.Count).End(xlUp).Row to 2 Step -1
'Assumes Header Row
For cnt 2 = 1 to Range("A" & cnt) - 1
Range("A" & cnt) = 1
Rows(cnt).Copy
Rows(cnt + 1).Insert Shift:=xlDown
Next
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"SB" wrote:

I have a speard sheet that I recieved from a client for some vouchers we are
sending out to some of thier retail locations. It looks like this:

# of vouchers, dollar value, shipping address
5 100 123 street
30 50 456 ave
etc....

There a thousands of row and they need to be submitted as a batch file in
our system, however the batch system needs each voucher to be it's own row,
it cant read the "# of vouchers" and create that many, so for example the
first row is 5 vouchers it needs the dollar amount and address to be
displayed 5 times in seprate rows. How do I make excel look at the first
cell of each row and duplicate the row the number given in the fisrt cell?
I need excel to also recognize it has duplicated the row X number of times
and not to duplicate the duplicates.

Thanks,

Duplicate rows a given number of times in Excel?
 

Hi Charles,

This has worked a treat, Could you explain how it works as I'm new to
creating macro. I wish to see if I can edit it so I can have it as a user
input for the number of times to be duplicated as aposed to the data cell #1?

Cheers
--
Paul Wilson


"Charles Chickering" wrote:

Sub CreateBatchList()
Dim cnt As Long
Dim cnt2 As Long
For cnt = Range("A" & Rows.Count).End(xlUp).Row to 2 Step -1
'Assumes Header Row
For cnt 2 = 1 to Range("A" & cnt) - 1
Range("A" & cnt) = 1
Rows(cnt).Copy
Rows(cnt + 1).Insert Shift:=xlDown
Next
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"SB" wrote:

I have a speard sheet that I recieved from a client for some vouchers we are
sending out to some of thier retail locations. It looks like this:

# of vouchers, dollar value, shipping address
5 100 123 street
30 50 456 ave
etc....

There a thousands of row and they need to be submitted as a batch file in
our system, however the batch system needs each voucher to be it's own row,
it cant read the "# of vouchers" and create that many, so for example the
first row is 5 vouchers it needs the dollar amount and address to be
displayed 5 times in seprate rows. How do I make excel look at the first
cell of each row and duplicate the row the number given in the fisrt cell?
I need excel to also recognize it has duplicated the row X number of times
and not to duplicate the duplicates.

Thanks,