Simple Macro to copy each row in the database "X" times? (Excel 2002)

A

AlanN

I was wondering if someone could help me with a macro to cut down on some repetitive task activity.

I have a regularly increasing DB of address information in Excel with several hundred entries with row 1 as the header row. Depending on the demand, we have to create mail labels in various quantities per address, consecutively. I foresee a macro that when kicked off will prompt the user to enter in the number of labels for each row and then duplicate each unique row that number of times (except the header row, of course).
It would be best if it creates the duplicate row list on another sheet. There is only 1 sheet in the workbook as it stands.
For example:
Name Addr1 Addr2 City State Zip
Billy Bob 123 High Rd. Hereville Florida 12345
Sally Smith 321 Low Rd. Nowhere Alaska 54321
Jimmy Dean 222 Middle Rd. Whoville Ohio 55555


If I was prompted for a # and typed in "3", then the following result would be required:

Name Addr1 Addr2 City State Zip
Billy Bob 123 High Rd. Hereville Florida 12345
Billy Bob 123 High Rd. Hereville Florida 12345
Billy Bob 123 High Rd. Hereville Florida 12345
Sally Smith 321 Low Rd. Nowhere Alaska 54321
Sally Smith 321 Low Rd. Nowhere Alaska 54321
Sally Smith 321 Low Rd. Nowhere Alaska 54321
Jimmy Dean 222 Middle Rd. Whoville Ohio 55555
Jimmy Dean 222 Middle Rd. Whoville Ohio 55555
Jimmy Dean 222 Middle Rd. Whoville Ohio 55555


Assume that there the possibility for more columns to be added over time as well, so it should copy the whole row rather than a fixed range.

I would be much indebted if someone could help!

Many thanks, Alan
 
T

Tom Ogilvy

Sub makemultiplerows()
Dim rng As Range, cell As Range
Dim rw As Long
Dim numRows As Variant
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
Worksheets.Add
rw = 1
numRows = InputBox("How many rows per record?")
If Not IsNumeric(numRows) Then Exit Sub
For Each cell In rng
cell.EntireRow.Copy Destination:= _
ActiveSheet.Cells(rw, 1).Resize(numRows)
rw = rw + numRows
Next

End Sub

--
Regards,
Tom Ogilvy

I was wondering if someone could help me with a macro to cut down on some
repetitive task activity.

I have a regularly increasing DB of address information in Excel with
several hundred entries with row 1 as the header row. Depending on the
demand, we have to create mail labels in various quantities per address,
consecutively. I foresee a macro that when kicked off will prompt the user
to enter in the number of labels for each row and then duplicate each unique
row that number of times (except the header row, of course).
It would be best if it creates the duplicate row list on another sheet.
There is only 1 sheet in the workbook as it stands.
For example:
NameAddr1Addr2CityStateZip
Billy Bob123High Rd.HerevilleFlorida12345
Sally Smith321Low Rd.NowhereAlaska54321
Jimmy Dean222Middle Rd.WhovilleOhio55555


If I was prompted for a # and typed in "3", then the following result would
be required:

NameAddr1Addr2CityStateZip
Billy Bob123High Rd.HerevilleFlorida12345
Billy Bob123High Rd.HerevilleFlorida12345
Billy Bob123High Rd.HerevilleFlorida12345
Sally Smith321Low Rd.NowhereAlaska54321
Sally Smith321Low Rd.NowhereAlaska54321
Sally Smith321Low Rd.NowhereAlaska54321
Jimmy Dean222Middle Rd.WhovilleOhio55555
Jimmy Dean222Middle Rd.WhovilleOhio55555
Jimmy Dean222Middle Rd.WhovilleOhio55555


Assume that there the possibility for more columns to be added over time as
well, so it should copy the whole row rather than a fixed range.

I would be much indebted if someone could help!

Many thanks, Alan
 
Top