Autopopulate a column based on a cell value

[levans_1986]

Hello

I am creating a survey in an excel workbook where the first tab contains a unique number for each row (survey number) and then each column has a question with a yes/no answer. There is a second tab which only needs to be completed if for example the answer to the question in column P on sheet 1 is yes.

I want the second excel sheet to be able to autopopulate the survey number if the answer to the question is yes in column P of sheet 1. This means that it is easier for the person who is completing the survey. However I'm not sure how to do this

any help appreciated

thanks

laura

 

[levans_1986]

Thanks thats great.
I did as you said but was getting a run time error 1004, "autofilter method of range class failed"
I changed the code from your code which said
.Range("A1:P1").Range("$A$1:$P$1000").AutoFilter Field:=16, Criteria1:="Yes"
to
.Range("A1:P1").AutoFilter Field:=16, Criteria1:="Yes"
That stopped the error and the macro runs as far as applying the autofilter

then I get another error runtime error 91, "object variable or with block variable not set"
On pressing debug this takes me to the row
AutoFilter.Range.Offset(1, 0).Copy Destination:=Worksheets("sheet2").Range("A" & LRow)

Have I screwed up here by removing the range reference?

Sorry for so many questions, I am hoping we are nearly there??