Advance Filter

[mansoor2840]

Dear All
Need Urgent Help, I have a worksheet with 15 rows when i enter in row 1 i need it to advance filter data which is stored in master sheet and when i enter data in row 2 the filter data of row 1 should clear and it should filter on the value of row 2 similarly for all 15 rows
Criteria for advance filter will be Sheet2 ROW A12, sheet2 ROW A13 and so on.
Data stored in sheet name stock

Rows are in sheet 2 name del
thanks in advance

 

[mansoor2840]

please any help

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Yongle]

In sheet containing 15 rows

Rows 1 and 2 = current criteria
Row1 are HEADERS that exactly match headers above data
If you are wanting to criteria between 2 values (eg dates) then the header must be repeated
Row2 leave EMPTY

Below row 3
is where you put your criteria

When any cell in active row is double-clicked, the macro ...
- copies the active row to row 2
- clears prior filter
- applies advanced filter to sheet named Data columns A:D (amend as required)

Paste into the CRITERIA sheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row < 3 Then Exit Sub
    Application.ScreenUpdating = False
    Rows(Target.Row).Copy Range("A2")
    On Error Resume Next
    With Sheets("[COLOR=#ff0000]Data[/COLOR]")
        .ShowAllData
        .Range("[COLOR=#ff0000]A:D[/COLOR]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("1:2"), Unique:=False
    End With
    Cancel = True
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#FFFF00]Name[/td][td=bgcolor:#FFFF00]Date[/td][td=bgcolor:#FFFF00]Date[/td][td=bgcolor:#FFFF00]Type[/td][td=bgcolor:#FFFF00]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FFFF00]name01[/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00]>300[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]>200[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]name01[/td][td][/td][td][/td][td][/td][td]>300[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Criteria[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]date[/td][td]Type[/td][td]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Name01[/td][td]

23/12/2018​

[/td][td]

1​

[/td][td]

448​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Name01[/td][td]

21/12/2018​

[/td][td]

1​

[/td][td]

426​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]Name01[/td][td]

23/12/2018​

[/td][td]

1​

[/td][td]

357​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]