Microsoft 365 Automatisation de la date et des lignes sur Feuil1

Option Explicit
Private Sub Workbook_Open()
   Feuil1.[A1].Value = Feuil1.Name & " " & Format(Date + 1, "dd/mm/yyyy")
   End Sub

Private Sub Worksheet_Activate()

Dim Tb, L, NBl As Long, Nom As String
Application.ScreenUpdating = False
     Tb = Array(Array([TS_VOL_PAX_Végétaux].Rows.Count, [VOL_PAX_Végétaux]), _
                Array([TS_VOL_PAX_PES_PEM].Rows.Count, [VOL_PAX_PES_PEM]), _
                Array([TS_TRUCK].Rows.Count, [TRUCK]), _
                Array([TS_FULL_FREIGHTER].Rows.Count, [FULL_FREIGHTER]), _
                Array([TB_STOCKEUR].Rows.Count, [STOCKEUR]), _
                Array([TS_PREVISIONS_LHD].Rows.Count, [PREVISIONS_LHD]))
                
     For Each L In Tb
          NBl = L(1).Rows.Count
          Nom = L(1).Name.Name
          Select Case True
               Case L(0) < NBl
                    L(1).Rows(L(0) + 1).Resize(NBl - L(0)).EntireRow.Delete
               Case L(0) > NBl
                    
                    L(1).Offset(NBl).Resize(L(0) - NBl).EntireRow.Insert
                    ThisWorkbook.Names.Add Nom, L(1).Resize(L(0))
                    Set L(1) = Evaluate(Nom)
               Case L(0) = NBl
                    'Rien
          End Select
          L(1).Rows(1).Copy
          L(1).PasteSpecial Paste:=xlPasteFormats
          L(1).Borders.Weight = xlThick
          L(1).Borders(xlInsideVertical).LineStyle = xlNone
          L(1).Borders(xlInsideHorizontal).LineStyle = xlNone
          L(1).Columns(L(1).Columns.Count).Borders(xlEdgeLeft).Weight = xlThick
     Next
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
Dim lig&, n%, w As Worksheet, R As Range
lig = 1
Application.ScreenUpdating = False
Cells.Delete 'RAZ
For n = 1 To 5 'les 5 premières feuilles
    Set w = Sheets(n)
    Set R = w.Rows("1:" & w.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row)
    R.Copy Rows(lig) 'copier-coller
    lig = lig + R.Rows.Count
Next n
Columns("A").ColumnWidth = 20
Columns("F").AutoFit 'colonne Observation
Columns("J:K").AutoFit 'colonnes Observation
End Sub