Dim rng as Range, cell as Range
Dim kk as Long, i as long
With Worksheets("sheet1")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End with
kk = 2
for each cell in rng
num = 0
Select Case lcase(cell.value)
Case "two"
Num = 2
Case "three"
Num = 3
Case "four"
Num = 4
End Select

for i = 1 to Num
cell.EntireRow.copy Destination:=Worksheets("Sheet2") _
.Cells(kk,1)
kk = kk + 1
next
Next

--
Regards,
Tom Ogilvy

"Patti" <here@spamNOT.net> wrote in message
news:42c41034_3@newspeer2.tds.net...
> I have a sheet in which every row needs to be copied to a new sheet, but a
> variable number of times. Example (source sheet):
>
> Column A Column B
> "Two" Pete
> "Three" John
> "Three" Cindy
>
> I want to look at *text* in column A and say "if A1 is Two then copy this
> row to DestinationSheet 2 times, if text is Three copy 3 times." There
> will only be 2 or 3 different conditions. When the loop is complete,
> DestinationSheet would look like:
>
> Column A Column B
> "Two" Pete
> "Two" Pete
> "Three" John
> "Three" John
> "Three" John
> "Three" Cindy
> "Three" Cindy
> "Three" Cindy
>
> What is the most efficient way to do this?
>
> Thanks in advance!
>
> Patti
>
>