Advance Filter using VBA

[Svmaxcel]

I have data in column A,b,c its a huge list.
Example :
Sheet 1
Headings.
Let's say column A is System type like Desktop/Laptop.
Column B is System model like ABC/XYZ/PQR.
Column C has part # like 1234/5678/789/345...

Requirements Sheet2.
By default all column A,B,C should have all data as it is in Sheet1.

Now if I click on any System type (column A), it should auto filter the list in Column B and C.
If I click on any specific System Model(column B), Column 3 should display list corresponding to the selection

[mdmackillop]

Revised for successive filter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column < 4 And Target.Row > 4 And Target <> "" Then
        Cells(2, Target.Column) = Target
        Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1").CurrentRegion, Unique:=False
        With Sheets(2)
            .Cells.ClearContents
            Range("A4").CurrentRegion.Copy .Cells(1, 1)
        End With
    Else
        Range("A2:C2").ClearContents
        On Error Resume Next
        ActiveSheet.ShowAllData
    End If
End Sub

[mana]

autofilter

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    If Intersect(Target, Cells(1).CurrentRegion) Is Nothing Then Exit Sub
    
    Sheets("Sheet2").Cells(1).CurrentRegion.ClearContents
     
    With Cells(1).CurrentRegion
        .AutoFilter Target.Column, Target.Value
        .Copy Sheets("Sheet2").Cells(1)
    End With
    
    Cancel = True
    
End Sub

[Svmaxcel]

Thanks for help.
I am looking for something like this.
Assume sheet 1 for data
Sheet 2 will have the same column heading and everything will be displayed.
But in sheet 2 if I click on any cell it should filter accordingly.
File attached.

[mdmackillop]

You've made no comment on the proposed solutions posted.

But in sheet 2 if I click on any cell it should filter accordingly.

As you have sheet names, please use them. I assume Sheet 2 is Filter
There are only headers in sheet Filter in your example so how can this work?

[Svmaxcel]

The sheet you made was awesome
How ever I want that the raw data should be in sheet 1 and sheet 2 should be the filter.
I mean sheet 1 will be hidden, but users can use sheet2 for filtering data

[mdmackillop]

Put my code in your Filter sheet code module with layout as per my example.

[Svmaxcel]

Pasted the code didn't work, can you please help with an example file, if possible.

[mana]

Filter sheet module

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     
      Unprotect
     
    If Intersect(Target, Cells(2).CurrentRegion) Is Nothing Then
        Sheets("Data").Cells(1).CurrentRegion.Copy Cells(2)
        AutoFilterMode = False
    Else
        Cells(2).CurrentRegion.AutoFilter Target.Column - 1, Target.Value
    End If

    Cancel = True
    Protect
     
End Sub

マナ

[mdmackillop]

If you remove row and column headers, make column A narrow and move your data location then not surprisingly it may go unnoticed that the code needs to be amended. For the future, keep your samples basic and fix the appearance once things are working.