wrote in message
oups.com...
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

Iain,

Thanks, this does work beautifully for the example I have given. Since I
actually have many columns of data,I am wondering, though, if there is a way
to copy the whole row at once rather than:

thisworkbook.sheets(dest).cells(dr, 1).value=.cells(fr,1).value
thisworkbook.sheets(dest).cells(dr, 2).value=.cells(fr,2).value

Regards,

Patti