Exactly what I need, Tom. Thanks to all of you!

Patti


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23fwSM3YfFHA.352@TK2MSFTNGP09.phx.gbl...
> 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
>>
>>
>
>