Fill in a row until bottom of the table

[twmills]

Hello,

I'm attempting to take the date in cell A2, and paste it (the value not the formula) in each cell in column A, starting in A9, but have it stop at the bottom of the table. In the attachment, the end result should have 3/15/2021 in cells A9 through A400.

However, next week another data set will be pasted below this one. I have the A2 formula structured to show 3/22/2021 starting next week. Next week's data will essentially be taking up cells B401 through P800. When I run the macro again, I need it to take the date in A2 (which will show 3/22/2021 starting next week), and have it pasted in column A in only the blank cells (A401:A800, along side the new data). Once again having it stop at the end of the table.

So after two weeks the end result should have 3/15/2021 from A9:A400, with its data to the right of it in columns B:P. Then have 3/22/2021 from A401:A800. Then so on and so on with each passing week. Don't worry about changing the date when testing, that will occur on it's own with each passing week based on how the formula is setup for it.

I hope that makes sense...Thanks so much in advance.

[SamT]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then FillDates
End Sub


Sub FillDates()
    Range(Cells(Rows.Count, "A").End(xlUp).Offset(1), Cells(Cells(Rows.Count, "B").End(xlUp).Row, "A")) = Range("$A$2").Value
End Sub

Assumes new B:P exists before A2 changes. Doesn't matter how deep B:P is.

[twmills]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then FillDates
End Sub


Sub FillDates()
    Range(Cells(Rows.Count, "A").End(xlUp).Offset(1), Cells(Cells(Rows.Count, "B").End(xlUp).Row, "A")) = Range("$A$2").Value
End Sub

Assumes new B:P exists before A2 changes. Doesn't matter how deep B:P is.

Exactly what I was looking for.

Thanks!!