Sub copyABCRow()
Dim i&, j&, k&, iRow&, iCol&
Dim Arr, dArr
With Worksheets("ALL")
Arr = .Range("B1:B" & .Range("B65000").End(xlUp).Row).Value
For i = 8 To UBound(Arr) Step 7
.Cells(i + 5, 3).Resize(2, 30).Formula = reFormula(i)
Next i
End With
End Sub
Private Function reFormula(i As Long) As Variant
Dim Arr(1 To 30, 1 To 2) As Variant
Arr(1, 1) = "=C" & i + 1: Arr(1, 2) = "=C" & i + 3
Arr(2, 1) = "=D" & i + 1: Arr(2, 2) = "=D" & i + 3
Arr(3, 1) = "=SUM(F" & i + 5 & ":AF" & i + 5 & ")": Arr(3, 2) = "=SUM(F" & i + 6 & ":AF" & i + 6 & ")"
Arr(4, 1) = "=F" & i & "-SUM(F" & i + 1 & ":F" & i + 2 & ")+G" & i: Arr(4, 2) = "=F" & i & "-SUM(F" & i + 3 & ":F" & i + 4 & ")+G" & i
Arr(6, 1) = "=H" & i & "-SUM(H" & i + 1 & ":H" & i + 2 & ")+I" & i: Arr(6, 2) = "=H" & i & "-SUM(H" & i + 3 & ":H" & i + 4 & ")+I" & i
Arr(8, 1) = "=J" & i & "-SUM(J" & i + 1 & ":J" & i + 2 & ")+K" & i: Arr(8, 2) = "=J" & i & "-SUM(J" & i + 3 & ":J" & i + 4 & ")+K" & i
Arr(10, 1) = "=L" & i & "-SUM(L" & i + 1 & ":L" & i + 2 & ")+M" & i: Arr(10, 2) = "=L" & i & "-SUM(L" & i + 3 & ":L" & i + 4 & ")+M" & i
Arr(12, 1) = "=N" & i & "-SUM(N" & i + 1 & ":N" & i + 2 & ")+O" & i: Arr(12, 2) = "=N" & i & "-SUM(N" & i + 3 & ":N" & i + 4 & ")+O" & i
Arr(14, 1) = "=P" & i & "-SUM(P" & i + 1 & ":P" & i + 2 & ")+Q" & i: Arr(14, 2) = "=P" & i & "-SUM(P" & i + 3 & ":P" & i + 4 & ")+Q" & i
Arr(16, 1) = "=R" & i & "-SUM(R" & i + 1 & ":R" & i + 2 & ")+S" & i: Arr(16, 2) = "=R" & i & "-SUM(R" & i + 3 & ":R" & i + 4 & ")+S" & i
Arr(18, 1) = "=T" & i & "-SUM(T" & i + 1 & ":T" & i + 2 & ")+U" & i: Arr(18, 2) = "=T" & i & "-SUM(T" & i + 3 & ":T" & i + 4 & ")+U" & i
Arr(20, 1) = "=V" & i & "-SUM(V" & i + 1 & ":V" & i + 2 & ")+W" & i: Arr(20, 2) = "=V" & i & "-SUM(V" & i + 3 & ":V" & i + 4 & ")+W" & i
Arr(22, 1) = "=X" & i & "-SUM(X" & i + 1 & ":X" & i + 2 & ")+Y" & i: Arr(22, 2) = "=X" & i & "-SUM(X" & i + 3 & ":X" & i + 4 & ")+Y" & i
Arr(24, 1) = "=Z" & i & "-SUM(Z" & i + 1 & ":Z" & i + 2 & ")+AA" & i: Arr(24, 2) = "=Z" & i & "-SUM(Z" & i + 3 & ":Z" & i + 4 & ")+AA" & i
Arr(26, 1) = "=AB" & i & "-SUM(AB" & i + 1 & ":AB" & i + 2 & ")+AC" & i: Arr(26, 2) = "=AB" & i & "-SUM(AB" & i + 3 & ":AB" & i + 4 & ")+AC" & i
Arr(28, 1) = "=AD" & i & "-SUM(AD" & i + 1 & ":AD" & i + 2 & ")+AE" & i: Arr(28, 2) = "=AD" & i & "-SUM(AD" & i + 3 & ":AD" & i + 4 & ")+AE" & i
Arr(30, 1) = "=AF" & i & "-SUM(AF" & i + 1 & ":AF3)": Arr(30, 2) = "=AF" & i & "-SUM(AF" & i + 3 & ":AF" & i + 4 & ")"
reFormula = Application.Transpose(Arr)
End Function