Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I DUPLICATE ONE SHEET MULTIPLE TIMES | Excel Discussion (Misc queries) | |||
How to Autonumber Rows but Retain Same Number for Duplicate Entrie | Excel Discussion (Misc queries) | |||
Return Time for Duplicate Files & Times | Excel Worksheet Functions | |||
Duplicate entry "x" number of times | Excel Discussion (Misc queries) | |||
How do I duplicate a sheet 20 times in an excel spreadsheet | Excel Worksheet Functions |