Matricule alphanumérique en associant le nom de l’onglet, l’année en cours et un compteur de trois c

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim pl As Range, c As Range
    Select Case Sh.Name
    Case "MAS", "DRK", "KLM"
        Set pl = Intersect(Target, Sh.Columns(2), Sh.[2:999])    '999 car limité à 3 chiffres
        If Not pl Is Nothing Then
            For Each c In pl
                If c = "" Then
                    c.Offset(, -1) = ""
                Else
                    With c.Offset(, -1)
                        Application.EnableEvents = False
                        .Value = Sh.Name & Year(Date) & Format(.Row - 1, "000")
                        Application.EnableEvents = True
                    End With
                End If
            Next c
        End If
    End Select
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim pl As Range, c As Range
    Select Case Sh.Name
    Case "MAS", "DRK", "KLM"
        Set pl = Intersect(Target, Sh.Columns(2))    '
        If Not pl Is Nothing Then
             For Each c In pl
                    c.Offset(, -1) = IIf(c = "", "", Sh.Name & Year(Date) & Format(c.Row - 1, "####000"))
            Next c
        End If
    End Select
    Application.CutCopyMode = False
End Sub