copy rows meeting criteria to another worksheet

[Guest]

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

 

[Vasant Nanavati]

Not really possible without a macro.

 

[Guest]

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.

 

[Guest]

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

 

[Roger Govier]

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