VBA Copy Paste a Range in Next Available Column Then Add a Single Day to One of the Newly Pasted Cells Repeatedly

Question

I'm attempting to make a button with an attached macro that will copy a range of data and formulas, put them in the following column, and then add one day to a single cell. The issue I'm having is that I want to repeat the process, adding one date from each newly added cell to the previously pasted one. The concept is that when I click the button, the pasted block adds one day to the next specified cell after pasting the original range into that cell. After adding data to that new pasted block, I click the button again to receive a new pasted block with the next specific cell added to it.

So far I have this:

Sub PasteToNextEmptyColumn()

    Application.ScreenUpdating = False
        
    Range("A4:C14").Copy
    ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteColumnWidths
    ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteAll

    Range("E9").Value = DateAdd("d", 1, (Range("E9")))


    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

Answer 1

With a date you can treat it like a value and just put:

• Range("E9") = Range("E9") + 1 ' this will input the date in the same cell E9

Maybe ensure the date is in DD/MM/YYYY or another recognized format.

I hope this helps you.