Array Defined by Cell Entry

[helpexcel]

I'm using this code to filter a list on ws2 based on lists on ws1. I have 10 lists each with different variables. I want to be able to put a validation list in a cell on ws2 with the names of each list on ws1. This input would then be used to define "Ary" below. Is this just a matter of using 10 if/else/thens, or is there an easier way to do it?

Thanks!

Ary = Application.Transpose(ws1.Range(("A3"), ws1.Range("A3").End(xlDown)))
With ws2
.Range("A:A").AutoFilter 1, Ary, xlFilterValues
End With

 

[helpexcel]

I tried the IF/THEN/ELSE Methind and i'm getting an error on the * line saying "AutoFilter method of Range class Failed." Any ideas on how to correct?

If ThisWorkbook.Worksheets("Sheet1").Range("C1") = "Apple" Then
Ary = Application.Transpose(ws1.Range(("A3"), ws1.Range("A3").End(xlDown)))

ElseIf ThisWorkbook.Worksheets("Sheet1").Range("C1") = "Orange" Then
Ary = Application.Transpose(ws1.Range(("B3"), ws1.Range("B3").End(xlDown)))

ElseIf ThisWorkbook.Worksheets("Sheet1").Range("C1") = "Pear" Then
Ary = Application.Transpose(ws1.Range(("C3"), ws1.Range("C3").End(xlDown)))

End If

With Sheets("Sheet2")
* .Range("A:A").AutoFilter 1, Ary, xlFilterValues
End With