XL 2016 alimenter des onglets en fonction du nom figurant en colonne G

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim lig&, r As Range
Application.ScreenUpdating = False
With Sheets("planning")
    If Sh.Name = .Name Then Exit Sub
    .[A:K].AutoFilter 7, Sh.Name 'filtre automatique
    lig = 2
    For Each r In .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows
        Sh.Cells(lig, 1) = r.Cells(1).MergeArea(1)
        Sh.Cells(lig, 2) = r.Cells(3)
        Sh.Cells(lig, 3) = r.Cells(8)
        Sh.Cells(lig, 4) = r.Cells(9)
        lig = lig + 1
    Next
    .[A:K].AutoFilter 'RAZ
End With
Sh.Range("A" & lig & ":D" & Sh.Rows.Count).ClearContents 'RAZ en dessous
Sh.[A:A].NumberFormat = "dddd dd/mm/yyyy"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim t, BCM, n&, i&, deb!
   deb = Timer
   Application.ScreenUpdating = False
   With Sheets("planning")
      If Sh.Name = .Name Then Exit Sub
      If .FilterMode Then .ShowAllData
      t = .Range("a1").Resize(.Cells(.Rows.Count, "g").End(xlUp).Row, 9)
   End With
   i = 1: t(i, 2) = t(i, 3): t(i, 3) = t(i, 8): t(i, 4) = t(i, 9):  BCM = Sh.Name: n = n + 1
   For i = 2 To UBound(t)
      If t(i, 1) = "" Then t(i, 1) = t(i - 1, 1)
      If t(i, 7) = BCM Then n = n + 1: t(n, 1) = t(i, 1): t(n, 2) = t(i, 3): t(n, 3) = t(i, 8): t(n, 4) = t(i, 9)
   Next i
   With Sh
      .Range(.Range("a2"), .Cells(.Rows.Count, 5)).ClearContents
      .Range("a2").Resize(n, 4) = t
   End With
   MsgBox Format(Timer - deb, "0.000\ sec.")
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim tablo, critere$, i&, n&, dat
With Sheets("planning")
    If Sh.Name = .Name Then Exit Sub
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    tablo = .Range("A1:I" & .Range("G" & .Rows.Count).End(xlUp).Row).Value2 'matrice, plus rapide
End With
critere = UCase(Sh.Name)
For i = 2 To UBound(tablo)
    If UCase(tablo(i, 7)) = critere Then
        n = n + 1
        If tablo(i, 1) <> "" Then dat = tablo(i, 1)
        tablo(n, 1) = dat: tablo(n, 2) = tablo(i, 3): tablo(n, 3) = tablo(i, 8): tablo(n, 4) = tablo(i, 9)
    End If
Next
'---restitution---
If Sh.FilterMode Then Sh.ShowAllData 'si la feuille est filtrée
Sh.[A:A].NumberFormat = "dddd dd/mm/yyyy" 'au cas où
With Sh.[A3] '1ère cellule de destination, à adapter
    If n Then .Resize(n, 4) = tablo
    .Offset(n).Resize(Sh.Rows.Count - n - .Row + 1, 4).ClearContents 'RAZ en dessous
End With
End Sub

=SI(OU(D2="";E2="";F2="");REPT(G2;OU(G2="TPG";G2="API"));CONCATENER("B";D2;"C";E2;"M";F2))

=SI(OU(D2="TPG";D2="API");D2;REPT(CONCATENER("B";D2;"C";E2;"M";F2);NON(NB.SI(D2:F2;""))))