Extending Range in Code

ahhhhhh, now I get you.

Ok, so what you want is for the code that copies the date over to choose the next empty column to write to, after column Z, not overwrite the value in column Z.

Do this:

lCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
if lcol < 26 then lcol = 26

What you're telling Excel is find the next available column number but if it's to the left of column Z make it column Z.