No correct output

I think you are over complicating things.

If you want to transfer the value from column B to column AG all you should need is this.

If Target.Row > 1 And Target.Column = 2 Then
    Cells(Target.Row, "AG").Value = Target.Value
End If