Patti wrote:
> 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

this should work:

dim fr as long, dr as long, numRows as long, i as long
dim from as string, dest as string

from="Sheet1" 'change these to whatever
dest="Sheet2"

fr=1
dr=0

with thisworkbook.sheets(from)
do
select case .cells(fr, 1).value 'column 1 = A
case "Two"
numRows=2
case "Three"
numRows=3
case else
numRows=1
end select
for i=1 to numRows
dr=dr+1
thisworkbook.sheets(dest).cells(dr, 1).value=.cells(fr,1).value
thisworkbook.sheets(dest).cells(dr, 2).value=.cells(fr,2).value
next
fr=fr+1
loop until .cells(fr, 1).value=""
end with


Iain