Every time macro run on another sheet based on cell value (worksheet event)

[shiva_ram]

HI

I wrote below code but advanced filter is not working on "Filtered Data" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)



' first remove filter
'If Target.Cells.Count > 1 Then GoTo done
If Application.Intersect(Target, ActiveSheet.Range("g2")) Is Nothing Then GoTo done

Call Macro2
done:
Exit Sub
End Sub


Sub Macro2()
Dim ws As Worksheet
If ws.AutoFilterMode Then

ws.ShowAllData

End If

Set ws = Worksheets("Filtered Data")
ws.Range("A:BL").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("BN1:BS2"), Unique:=False
End Sub

Regards
shiva ram

 

[Alex Blakenburg]

See if changing your macro2 to the below fixes it.

Sub Macro2()
    Dim ws As Worksheet
    Set ws = Worksheets("Filtered Data")
    If ws.AutoFilterMode Then
        ws.ShowAllData
    End If
    
    ws.Range("A:BL").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
    :=ws.Range("BN1:BS2"), Unique:=False
End Sub