Recopie données de plusieurs colonne vers le bas en vba

DernLigne = Range("A" & rows.count).End(xlUp).Row

Sub Tech_par_activitéTER()' Nombre de tech par activité
Dim DernLigne As Long
'Entêtes
Range("Y1:AF1") = Array("Front", "Back", "Nbre en CA", "Formation", "Réunion", "IRP", "Maladie", "Soutien")
'Front et Back
Range("Y2:Z2").FormulaR1C1 = "=IF(RC[-19]=0,"""",IF(RC[-19]>300,1,0.5))"
' Congés (addition de plusieurs critères)
Range("AA2").FormulaR1C1 = "=IF(SUM(RC[-16]:RC[-12])=0,"""",IF(SUM(RC[-16]:RC[-12])>300,1,0.5))"
'Formation
Range("AB2").FormulaR1C1 = "=IF(RC[-11]=0,"""",IF(RC[-11]>300,1,0.5))"
'Réunion
Range("AC2").FormulaR1C1 = "=IF(RC[-7]=0,"""",IF(RC[-7]>300,1,0.5))"
'IRP et Maladie
Range("AD2:AE2").FormulaR1C1 = "=IF(RC[-12]=0,"""",IF(RC[-12]>300,1,0.5))"
'Soutien
Range("AF2").FormulaR1C1 = "=IF(RC[-8]=0,"""",IF(RC[-8]>300,1,0.5))"
'Recopie des formules jusqu'à la dernière ligne non vide
DernLigne = Cells(Rows.Count, 1).End(3).Row
Range("Y2:AF" & DernLigne).FillDown
End Sub

Sub Tech_par_activitéTERBIS()
Dim DernLigne As Long, col, i As Byte
col = Array("Y2:Z2", 19, "AB2", 11, "AC2", 7, "AD2:AE2", 12, "AF2", 8)
'Entêtes
Range("Y1:AF1") = Array("Front", "Back", "Nbre en CA", "Formation", "Réunion", "IRP", "Maladie", "Soutien")
Range("AA2").FormulaR1C1 = "=IF(SUM(RC[-16]:RC[-12])=0,"""",IF(SUM(RC[-16]:RC[-12])>300,1,0.5))"
For i = LBound(col) To UBound(col) Step 2
Range(col(i)).FormulaR1C1 = "=IF(RC[-" & col(i + 1) & "]=0,"""",IF(RC[-" & col(i + 1) & "]>300,1,0.5))"
Next
DernLigne = Cells(Rows.Count, 1).End(3).Row
Range("Y2:AF" & DernLigne).FillDown
End Sub