copy row a specific number times

Posted by kpont on May 16, 2001 9:27 AM

I'd like to take individual rows and copy them a certain number of times. Is it posible to make a macro that will prompt with a dialog box to copy the row a certain number of times. I know this can be done with fill but I'd like to simplify the prcoess.
Thanks Much!

Posted by Barrie Davidson on May 16, 2001 9:31 AM

Where do you want the rows copied to?

Posted by kpont on May 16, 2001 11:16 AM

I'd like to copy them to a new sheet. So if I have three rows of data and I want 350 copies of row 1, 250 copies of row 2, and 700 copies of row 3, all copied to a new single sheet. I need to figure out how to do it more easily than fill, copy and paste to new sheet.

Posted by Barrie Davidson on May 16, 2001 12:01 PM

The following code assumes that you want to run through each row, starting at cell A1, until there is not data in column A.

Sub Copy_Row()
'Written by Barrie Davidson
Dim NRow As Integer
Dim CurrentRow As Integer
Dim SheetName As String
Dim Datasheet As String

Datasheet = ActiveSheet.Name
ActiveWorkbook.Sheets.Add after:=Sheets(Datasheet)
SheetName = ActiveSheet.Name
Sheets(Datasheet).Select
Range("A1").Select
Do Until Selection.Value = ""
CurrentRow = Selection.Row
NRow = InputBox("Current row selected is " & CurrentRow & Chr(13) & _
"Enter Number of Copies Required")
Selection.EntireRow.Copy
Sheets(SheetName).Select
ActiveCell.Range("A1:A" & NRow).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A" & NRow).Offset(1, 0).Select
Sheets(Datasheet).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I hope this is what you are looking for. If not, let me know and I'll try to get it right for you.
Barrie

Posted by kpont on May 16, 2001 1:36 PM

That is precisely what I needed. Thanks very much. You've just saved me a load of work! Thanks very much!!