Hi,

The easiest way for all these sorts of tasks is to use Data Filter


First put column labels above each of the columns in your data range, i.e. in A4:K4
Apply a dynamic range name to your data so that you don't need to worry about new records being added. i.e.

your name = =OFFSET(Sheet1!A4,0,0,COUNTA(Sheet1!A:A),11)

Now create a range of column labels on your output sheet. e.f. in say A5:G5 on sheet 2 copy your sheet 1 column labels. You don't need to copy them all if you only want a subset. Name this range of labels say "DataOut"

Create a Criteria range in say A1:A2 on sheet 2 with the column G label in A1 and the word "yes" in A2. Name A1:A2 say "Crit"

Now use the following macro

Sub FilterData
   Sheet1.Range("Data").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheet2.Range("DataOut"), criteriarange:=Sheet2.Range("Crit")

End Sub