Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim P As Range, Q As Range, lig&, r As Range, i&, refcol As Range, j As Variant
If Not IsNumeric(Sh.Name) Then Exit Sub
Application.ScreenUpdating = False
Sh.Protect "123", UserInterfaceOnly:=True 'la protection permet les modifications par macro
Sh.Rows.Hidden = False 'affiche tout
Sh.Rows("5:" & Sh.Rows.Count).ClearContents 'RAZ
On Error Resume Next 'si aucune SpecialCell
With Sheets("BASE")
.Protect "123", UserInterfaceOnly:=True 'déprotection nécessaire avec les SpecialCells
Select Case Val(Sh.Name)
Case 1: Set P = .Columns("D:E") 'CA + Bureau
Case 2: Set P = .Columns("C")
Case 3: Set P = .Columns("G")
Case 4: Set P = .Columns("F")
End Select
Set Q = Sh.Rows(4).SpecialCells(xlCellTypeConstants, 2)
lig = 4
For Each r In Intersect(P, P.SpecialCells(xlCellTypeConstants, 2).EntireRow).Rows 'ligne de 1 ou 2 cellules
If Application.CountIf(r, "x") Then 'NB.SI
lig = lig + 1
i = r.Row
Sh.Cells(lig, 2) = .Cells(i, 1)
For Each refcol In Q
j = Application.Match(refcol, .Rows(2), 0)
If IsNumeric(j) Then Sh.Cells(lig, refcol.Column) = .Cells(i, j)
Next refcol
End If
Next r
End With
Sh.Rows("4:" & lig).Sort Sh.Columns(3), xlAscending, Header:=xlYes 'tri sur les noms
i = Sh.Cells.SpecialCells(xlCellTypeLastCell).Row 'dernière ligne du UsedRange
If i > lig Then Sh.Rows(lig + 1 & ":" & i).Hidden = True 'masque les lignes vides
ActiveWindow.ScrollRow = 5 'cadrage
End Sub