Loop/copy rows variable times to new sheet

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

<iainking@gmail.com> wrote in message
news:1120146574.727824.254240@z14g2000cwz.googlegroups.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

Patti wrote:
> <iainking@gmail.com> wrote in message
> news:1120146574.727824.254240@z14g2000cwz.googlegroups.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

..cells(fr,1).entirerow.copy
..cells(dr,1).paste

though it might be better to use pastespecial, pasting only values

Iain

>cells(fr,1).entirerow.copy
>.cells(dr,1).paste

>though it might be better to use pastespecial, pasting only values

>Iain

Of course, line 2 should be:

thisworkbook.sheets(dest).cells(dr,1).paste

Iain