Record Changing Value for Multiple Cells

This should be all you need. Get rid of everything else. This will work on every cell in column AG. Your code didn't stop at column AI. It kept putting values in the next unused column, so that's what I did here. If you need to limit to only certain rows then more code will need to be added:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Target.Column = Range("AG1").Column Then
    Set r = Target.Offset(0, 2)
        Do Until r.Value = ""
            Set r = r.Offset(0, 1)
        Loop
        r.Value = Target.Value
End If

End Sub

Hope this helps!