Copy cell to end of sheet

[HomePro]

I am trying to copy the formula in da2 to the right based on the # of columns used in row 1.
Row one will have the header inforamtio0n and as I add additional headers i would like the sheet to fill to that location.
cant get tit to work though...

Sub zzzz()
Range("dA2").Select
Selection.Copy
Range("DA2", Cells(2, Columns.Count).End(xlToLeft)).Select
ActiveSheet.Paste
End Sub

 

[Joe4]

Try this:

Sub zzzz()
    Range("DA2").Copy Range(Cells(2, 105), Cells(2, Cells(1, Columns.Count).End(xlToLeft).Column))
End Sub

 

[HomePro]

that does it --- tks

 

[Joe4]

You are welcome.