Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create multiple copies of rows based on a cell value
I'm trying to create an input file for a mail merge that will create multiple
copies of rows based on a cell value. For example my sheet1 has: name address qty henry 123 anystreet 5 I need a sheet2 that contains 5, (the qty), rows of the name and address. Each record may have a different qty. Hope I have explained this well enough. TIA, Henry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create multiple copies of rows based on a cell value
Henryl,
Try this: Option Explicit Sub temp() Dim LastRow As Long Dim Qty As Integer Dim i As Long Dim j As Long Dim k As Long j = 2 LastRow = Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row For i = 2 To LastRow With Sheets("Sheet1") Qty = Cells(i, 3) .Range(Cells(i, 1), Cells(i, 2)).Copy End With With Sheets("Sheet2") For k = j To j + Qty - 1 .Range(.Cells(k, 1), .Cells(k, 2)).PasteSpecial Next k End With j = j + Qty Next i End Sub Art "henryl" wrote: I'm trying to create an input file for a mail merge that will create multiple copies of rows based on a cell value. For example my sheet1 has: name address qty henry 123 anystreet 5 I need a sheet2 that contains 5, (the qty), rows of the name and address. Each record may have a different qty. Hope I have explained this well enough. TIA, Henry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create multiple copies of rows based on a cell value
Sub Test()
Dim iLastRow As Long Dim iRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row iRow = 1 For i = 1 To iLastRow Range("A" & i & ":B" & i).Copy Worksheets("Sheet2") _ .Range("A" & iRow & ":B" & iRow + Range("C" & i).Value - 1) iRow = iRow + Range("C" & i).Value Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "henryl" wrote in message ... I'm trying to create an input file for a mail merge that will create multiple copies of rows based on a cell value. For example my sheet1 has: name address qty henry 123 anystreet 5 I need a sheet2 that contains 5, (the qty), rows of the name and address. Each record may have a different qty. Hope I have explained this well enough. TIA, Henry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup & return multiple rows based on cell value | Excel Worksheet Functions | |||
why does excel create multiple copies (as many as 18) of a file? | Excel Discussion (Misc queries) | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
Create a report with selected rows based on the content in a cell | New Users to Excel | |||
Why does Excel create multiple copies of the same file? | Excel Discussion (Misc queries) |