XL 2021 Remplir des listes à partir d'une base de données

job75 à dit:

Bonsoir chajmi, le forum,

En effet ALS35 a raison, il suffit de quelques corrections, le code dans Thisworkbook :

VB:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim P As Range, numlig As Range, i As Variant, numcol As Range
If Not IsNumeric(Sh.Name) Then Exit Sub
Application.ScreenUpdating = False
Sh.[C6].Resize(Sh.Rows.Count - 5, Sh.Columns.Count - 2).ClearContents 'RAZ
On Error Resume Next 'si aucune SpecialCell
Set P = Sh.Columns(2).SpecialCells(xlCellTypeConstants, 1)
With Sheets("BASE")
    For Each numlig In P
        i = Application.Match(numlig, .Range("A5:A" & .Rows.Count), 0)
        For Each numcol In Sh.Rows(2).SpecialCells(xlCellTypeConstants, 1)
            If IsNumeric(i) Then Sh.Cells(numlig.Row, numcol.Column) = .Cells(i + 4, numcol)
    Next numcol, numlig
End With
P.EntireRow.Sort Sh.Columns(3), xlAscending, Sh.Columns(2), , xlAscending, Header:=xlNo 'tri sur 2 colonnes
ActiveWindow.ScrollRow = 6 'cadrage
End Sub

Activez les feuilles 1 2 3 4.

A+

Cliquez pour agrandir...