copy rows meeting criteria to another worksheet

Hi,

I want to copy rows from 1 worksheet to another but only if they value in
column A= 'Yes'
So for example In worksheet1 I have 10 rows. 6 of those have 'Yes' in column
A and 4 have 'No' in column A.
In worksheet2 I would like to see only those 6 rows from worksheet1 which
had 'Yes' in column A.

I would like to do this using worksheet functions if possible, not macros.
also filtering is not good enough, I want to actually eliminate all those
rows that are 'No'

thanks for any help
Cheers

Not really possible without a macro.

--

Vasant


"confused" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I want to copy rows from 1 worksheet to another but only if they value in
> column A= 'Yes'
> So for example In worksheet1 I have 10 rows. 6 of those have 'Yes' in
> column
> A and 4 have 'No' in column A.
> In worksheet2 I would like to see only those 6 rows from worksheet1 which
> had 'Yes' in column A.
>
> I would like to do this using worksheet functions if possible, not macros.
> also filtering is not good enough, I want to actually eliminate all those
> rows that are 'No'
>
> thanks for any help
> Cheers

I don't understand why the autofilter will not work. If you filter the yes
values, you can copy the entire filtered list to sheet2 (Excel will not copy
the hidden rows that contain no values).

Alternatively, if you want the no's gone completely, filter the no values
(so yes values are hidden). Select the filtered list of no values and delete
the entire row. Then turn off the autofilter.

Based on the info you've given, this seems like the best solution. Be sure
to back up your data.

"confused" wrote:

> Hi,
>
> I want to copy rows from 1 worksheet to another but only if they value in
> column A= 'Yes'
> So for example In worksheet1 I have 10 rows. 6 of those have 'Yes' in column
> A and 4 have 'No' in column A.
> In worksheet2 I would like to see only those 6 rows from worksheet1 which
> had 'Yes' in column A.
>
> I would like to do this using worksheet functions if possible, not macros.
> also filtering is not good enough, I want to actually eliminate all those
> rows that are 'No'
>
> thanks for any help
> Cheers

Hi JMB,

Filtering doesnt work because I dont want to use copy and paste. I want the
rows in the new worksheet to be exact references to the original worksheet.
so for example, if someone pastes in new rows in the first worksheet these
would dispaly in the second

"JMB" wrote:

> I don't understand why the autofilter will not work. If you filter the yes
> values, you can copy the entire filtered list to sheet2 (Excel will not copy
> the hidden rows that contain no values).
>
> Alternatively, if you want the no's gone completely, filter the no values
> (so yes values are hidden). Select the filtered list of no values and delete
> the entire row. Then turn off the autofilter.
>
> Based on the info you've given, this seems like the best solution. Be sure
> to back up your data.
>
> "confused" wrote:
>
> > Hi,
> >
> > I want to copy rows from 1 worksheet to another but only if they value in
> > column A= 'Yes'
> > So for example In worksheet1 I have 10 rows. 6 of those have 'Yes' in column
> > A and 4 have 'No' in column A.
> > In worksheet2 I would like to see only those 6 rows from worksheet1 which
> > had 'Yes' in column A.
> >
> > I would like to do this using worksheet functions if possible, not macros.
> > also filtering is not good enough, I want to actually eliminate all those
> > rows that are 'No'
> >
> > thanks for any help
> > Cheers

Hi

It sounds like you need to use Advanced Filter, extracting the relevant
rows to a second sheet.
For help on this take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs

Regards

Roger Govier



confused wrote:

>Hi JMB,
>
>Filtering doesnt work because I dont want to use copy and paste. I want the
>rows in the new worksheet to be exact references to the original worksheet.
>so for example, if someone pastes in new rows in the first worksheet these
>would dispaly in the second
>
>"JMB" wrote:
>
>
>
>>I don't understand why the autofilter will not work. If you filter the yes
>>values, you can copy the entire filtered list to sheet2 (Excel will not copy
>>the hidden rows that contain no values).
>>
>>Alternatively, if you want the no's gone completely, filter the no values
>>(so yes values are hidden). Select the filtered list of no values and delete
>>the entire row. Then turn off the autofilter.
>>
>>Based on the info you've given, this seems like the best solution. Be sure
>>to back up your data.
>>
>>"confused" wrote:
>>
>>
>>
>>>Hi,
>>>
>>>I want to copy rows from 1 worksheet to another but only if they value in
>>>column A= 'Yes'
>>>So for example In worksheet1 I have 10 rows. 6 of those have 'Yes' in column
>>>A and 4 have 'No' in column A.
>>>In worksheet2 I would like to see only those 6 rows from worksheet1 which
>>>had 'Yes' in column A.
>>>
>>>I would like to do this using worksheet functions if possible, not macros.
>>>also filtering is not good enough, I want to actually eliminate all those
>>>rows that are 'No'
>>>
>>>thanks for any help
>>>Cheers
>>>
>>>