Why did you go back your old way? You cannot use that code, as that is hard-coded all the cells, and you don't want it to do that.
You need to borrow from the last code post I made, and the "last row" functionality I showed you earlier.
You need to get used to using variables if you want to make dynamic code.
So you would want your code to look something like this:
VBA Code:
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w
Dim r As Long
Dim lr As Long
' Find last row with entry in column D
lr = Cells(Rows.Count, "D").End(xlUp).Row
' If last row in column is less than 4, set r to 4, else add 12
If lr < 4 Then
r = 4
Else
r = lr + 12
End If
' Enter formulas on row r
Cells(r, "D").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Cells(r, "E").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
Cells(r, "I").FormulaR1C1 = "=AVERAGE(RC[-1]:R[11]C[-1])"
Cells(r, "J").FormulaR1C1 = "=MAX(RC[-2]:R[11]C[-2])"
End Sub
Note that I added comments to the code to tell you what is going on. Take the time to read through it, to see if it makes sense.