No, that is not the problem. I got it to return several values down the
column using your original statement. The problem is either in your criteria
set up, or your range that your are specifying to return the data from.
Again, if you are using the same range for your criteria that you are trying
to return data from, it won't work.

Your criteria has to be in one column and the data you want to return needs
to be in an adjacent column. Then it will return every cell value in the
adjacent column that corresponds to a criteria cell in the other column.

Unfortunately, the VBA help does not provide a lot of explanation on this.

"Mekinnik" wrote:

Correction to my post:
I can only get it to work on sheet1,e3

I think what I need to do is change the following line to include a column
range not a cell, but I'm not sure how to do it?

CopyToRange:=Range("E3"), Unique:=False

I thinking something to the effect of:
CopyToRange:=Range.(THE CELL WITH FOCUS), Unique:=False

"Mekinnik" wrote:

I am tring to populate a column of cells when they have focus and all I can
seem to do is populate just one and the kicker is that it only will populate
to the second cell in the reference range

I have a validation list on sheet1, e2 validated with data from
sheet2,a1-a50 and using the code for an advanced filter search I found
online, I can get it to work but only on sheet2,e3 and no others in the
column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 5 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("Lists").Range("F2").Calculate
Worksheets("Lists").Range("AbrevList").AdvancedFil ter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Lists").Range("F1:F2"), _
CopyToRange:=Range("E3"), Unique:=False
End If
End Sub



HELP PLEASE...