Ajout de lignes et Maj de TCD

Option Explicit

Sub Formules()
Dim DerL As Integer
DerL = Sheets(1).Range("A65536").End(xlUp).Row

    Range("E2:E" & DerL).Formula = _
        "=IF(RC[-4]="""","""",RIGHT(TEXT(YEAR(RC[-4]),0),2)&IF(MONTH(RC[-4])<10,CONCATENATE(""0"",TEXT(MONTH(RC[-4]),0)),TEXT(MONTH(RC[-4]),0)))"
    Range("F2:F" & DerL).Formula = _
        "=IF(RC[-5]="""","""",RIGHT(TEXT(YEAR(RC[-5]),0),2)&IF((INT(MOD(INT((RC[-5]-2)/7)+3/5,52+5/28))+1)<10,CONCATENATE(""0"",TEXT(INT(MOD(INT((RC[-5]-2)/7)+3/5,52+5/28))+1,0)),TEXT(INT(MOD(INT((RC[-5]-2)/7)+3/5,52+5/28))+1,0)))"
    Range("G2:G" & DerL).Formula = "=IF(RC[-6]="""","""",HOUR(RC[-6]))"
    Range("H2:H" & DerL).Formula = _
        "=IF(RC[-7]="""","""",IF(RC[-1]<6,""C"",IF(RC[-1]<14,""A"",""B"")))"
    Range("I2:I" & DerL).Formula = _
        "=IF(RC[-7]="""","""",RIGHT(TEXT(YEAR(RC[-7]),0),2)&IF(MONTH(RC[-7])<10,CONCATENATE(""0"",TEXT(MONTH(RC[-7]),0)),TEXT(MONTH(RC[-7]),0)))"
    Range("J2:J" & DerL).Formula = _
        "=IF(RC[-8]="""","""",RIGHT(TEXT(YEAR(RC[-8]),0),2)&IF((INT(MOD(INT((RC[-8]-2)/7)+3/5,52+5/28))+1)<10,CONCATENATE(""0"",TEXT(INT(MOD(INT((RC[-8]-2)/7)+3/5,52+5/28))+1,0)),TEXT(INT(MOD(INT((RC[-8]-2)/7)+3/5,52+5/28))+1,0)))"
    Range("K2:K" & DerL).Formula = "=IF(RC[-9]="""","""",HOUR(RC[-9]))"
    Range("L2:L" & DerL).Formula = _
        "=IF(RC[-10]="""","""",IF(RC[-1]<6,""C"",IF(RC[-1]<14,""A"",""B"")))"
    ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:= _
        "=OFFSET(RQ02!C1:C12,,,COUNTA(RQ02!C1))"
    MsgBox "La base contient " & DerL & " lignes", vbCritical, "Base TCD"
    Range("M1").Select
End Sub

Sub TCD()
ActiveWorkbook.RefreshAll
End Sub

Sub Formules()
Dim DerL As Integer, DerE As Integer
DerL = Sheets(1).Range("A65536").End(xlUp).Row
DerE = Sheets(1).Range("E65536").End(xlUp).Row
    Range("E" & DerE & ":" & "L" & DerL).Select
    Selection.FillDown
 
ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:= _
        "=OFFSET(RQ02!C1:C12,,,COUNTA(RQ02!C1))"
    MsgBox "La base contient " & DerL & " lignes", vbCritical, "Base TCD"
    Range("M1").Select
End Sub