Thread: Filter based on what's in another filter list

Hello,

I have a front sheet with a bunch of validation-list-drop-downs. When the user selects combinations of these and then runs a macro, a table on the second sheet is filtered by those criteria. Each row has a unique ID, let's called it the Sweet ID.

There is another third sheet which has more data (one row per Sweet ID, but with a different bunch of columns).

Here's what I would like to do: Run the macro which filters the second sheet, takes those Sweet IDs and then uses those to fitler the third sheet.

Grateful for any help. Thanks,

HE.

Hi HE

Try this code

Option Explicit
Sub Test()
    Dim LR As Long
    Dim LRm As Long
    Dim LRf As Long
    Application.DisplayAlerts = False
    Sheets("Fdata").Delete
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Fdata"
    With Sheets("List")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A11:A" & LR).SpecialCells(xlCellTypeVisible).Copy
        Sheets("Fdata").Range("A1").PasteSpecial
        Application.CutCopyMode = False
    End With
    LRf = Sheets("Fdata").Range("A" & Rows.Count).End(xlUp).Row
    With Sheets("M")
        .AutoFilterMode = False
        LRm = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A9:A" & LRm).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
                Sheets("Fdata").Range("A1:A" & LRf), Unique:=False
        .Activate
    End With
End Sub

Let me know of issues.

Hi HE
Hope you don't mind...I've rewritten the code for your "Reset All" button. If you like it use it...if not lose it

Sub showall()
    Dim Rng As Range
    With Sheets("Front")
        On Error Resume Next
        Set Rng = Union([Option_att_flag], [Option_prog_flag], [Option_proj_flag], [Option_pp_flag], _
                [Option_20plus_flag], [Option_10plus_flag], [Option_5plus_flag], [Option_age], [Option_type], [Option_ccc])
        Rng.Value = "Any"
        On Error GoTo 0
    End With
    With Sheets("List")
        If .FilterMode Then
            .ShowAllData
        End If
    End With
    With Sheets("M")
        If .FilterMode Then
            .ShowAllData
        End If
    End With
End Sub

@Jaslake

on error resume next
Sheets(array("List","M")).showalldata