Private Sub Worksheet_Activate()
Dim Rg As Range, Dest As Range
Dim C As Range, Crit As Range, plg As Range
With Worksheets("Nom")
Set plg = .Range("B3:C" & .Range("C65536").End(xlUp).Row)
End With
Application.ScreenUpdating = False
For a = 1 To 2
With Worksheets("Nom")
Select Case a
Case 1
Set Dest = Range("B5:C5")
Set Crit = .Range("D3:D4")
Case 2
Set Dest = Range("G5:H5")
Set Crit = .Range("H3:H4")
End Select
End With
With plg
.AdvancedFilter xlFilterInPlace, Crit
Worksheets("Nom").Range("_FilterDataBase").Offset(1).Resize(. _
Rows.Count + 1).SpecialCells(xlCellTypeVisible).Copy Dest
Worksheets(.Parent.Name).ShowAllData
End With
Dest.CurrentRegion.Offset(2).Sort Key1:=Dest.Item(1, 1), _
order1:=xlAscending, Header:=xlYes
Next
Application.ScreenUpdating = True
End Sub