Macro to autofilter if summary list is "Yes"

Hi All,

I have been looking through the forums for most of this morning and haven't come across a solution that I have managed to make work for me. I was hoping you could help and have attached my sample sheet.

In the sheet "Summary" i have options which my user must select yes or no to e.g.

Option 1 : Yes
Option 2 : Yes
Option 3 : No
Option 4 : Yes

Then in sheet "Output" i want to aAutofilter column A based on the Yes or No of sheet "Summary" i.e. only to show those items where it is marked as yes. I have managed to do this with hard coding to test that it can be done, and i get that to work fine. However when i try to introduce an array to store which options are Yes, so that i can use it in my autofilter, it doesn't work.

Please, any help would be great!!
I haven't used arrays in VB before and am really struggling, i am using Excel 2007.

Option Explicit
Sub FilterfromArray()
Dim MyArray(1) As Variant
With ActiveSheet
MyArray(0) = ("5")
MyArray(1) = ("2")
ActiveSheet.Range("A1:A274").AutoFilter Field:=1, Criteria1:=Application.Transpose(MyArray), _
Operator:=xlFilterValues
End With
End Sub

MANY THANKS!

I do not know why you want to need to transpose an array. The array in filter is used to filter a range with many options.
In your case, you want to filter by 5 and 2.
Use the first code if that is the intention.

Please Login or Register  to view this content.

If you just want to filter the "Yes" in column C, you can use this code.

Please Login or Register  to view this content.

My apologies for being vague. What i need to do is for those options marked as "Yes", i need to autofilter a separate sheet to only show those options. In my example
Option 1 : Yes
Option 2 : Yes
Option 3 : No
Option 4 : Yes

The second spreadsheet has 250 rows with options 1 to 33. This spreadsheet must just show option 1,2 and 4. I assumed i need an array to determine which options are marked yes but haven't been able to do it.

Please use code tags with your code as per forum's rule.
In which column are these "Yes"? Use that column using a criteria of yes to filter the sheet.

Hi AB33

Sorry i'm not sure what code tags are, i will check and do so.
I have attached my actual spreadsheet.

Sheet (Summary) column C has the Yes and No options the user must choose. Sheet (Loan and payment conditions)to autofilter and only show those option that are marked as Yes in the summary sheet.

I have been trying and If then statement but its such a nightmare.

Which column or what is the name of the column in sheet Loan and payment conditions you want to filter?
The columns Yes and No should be linked to Loan and payment conditions

Hi AB33

In sheet Summary i have numbered column A to be 1, 2, 3 etc. I want to show in sheet Loan and payment conditions i have numbered the rows appropriately, e.g. 111111,222222,33333 etc because there are multiple options for the conditions.

Yes the column Yes/No should be linked to loan and payment conditions, but only show those numbers where it is "Yes"

Thank you so much for trying to help and being so patient.

I think you want to filter by column B -Condition Type, but only those column which are "Yes" in the summary sheet.
Example: A1:A2,A18:A33:
If this what you wanted to do, a loop might offer you a better solution.