Let's say that you want to bring over cells between A2 and G50 from
Sheet1. Enter this formula in A2 of Sheet2

=IF(Sheet1!$H2="Yes",Sheet1!A2,"")

This can be copied across into B2:H2. Highlight the cells A2 to H2 and
copy them down into rows 3 to 50. You should now see entries only in
those cells where H on that row contains "Yes".

If you want the rows bunched up, so that empty rows are not visible,
then highlight column H and click Data | Filter | Autofilter (check) -
from the filter pull-down in H1, select "Yes", and only those rows will
now be visible.

Hope this helps.

Pete

One way to set it up dynamically using non array formulas ..

Assume source data in Sheet1's cols A to H, data from row2 down. The key col
is col H which contains for eg: Yes, No, etc

In Sheet2,

Assume A1 will house the desired input for the key col H, say: Yes

Put in B2:
=IF($A$1="","",IF(Sheet1!H2=$A$1,ROW(),""))

Copy B2 down to cover the max expected extent of data in Sheet1's col H, eg
down to B500. Leave B1 blank.

Put in C2:
=IF(ROW(A1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0)))

Copy C2 across to J2, then fill down by the smallest extent sufficient to
cover the max expected number of result lines for any input in A1, say down
to J100. Hide away col B. Cols C to J will return the required results from
Sheet1's cols A to H, with all result lines neatly bunched at the top.