Excel VBA Date Generation Loop

Just seen the comments. Yes Code Review would be good. You probably want to move the entire process into an array.

This demonstrates all the required elements.

Option Explicit
Public Sub GenerateDates()
    Const LOOPCOUNT As Long = 54
    Dim i As Long, j As Long
    Dim startDate As Long, endDate As Long, rowCounter As Long
    startDate = CLng(Now)
    endDate = startDate + 7
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = 1 To LOOPCOUNT
            For j = startDate To endDate
                rowCounter = rowCounter + 1
                .Cells(rowCounter, 1) = j
            Next j
             rowCounter = rowCounter + 5 '<== Add gap
        Next i
        .Columns("A").NumberFormat = "m/d/yyyy"
    End With
    Application.ScreenUpdating = True
End Sub

Doing the same thing in memory (I have included a second dimension as you may have additional columns in your data. My principle was really about showing the dates increment with row gap.)

Option Explicit
Public Sub GenerateDates() '697
    Const LOOPCOUNT As Long = 54      
    Dim i As Long, j As Long
    Dim startDate As Long, endDate As Long, rowCounter As Long
    startDate = CLng(Now)
    endDate = startDate + 7
    Dim ROWGAP As Long: ROWGAP = 41-(Enddate-StartDate)
    Dim outputArr()
    ReDim outputArr(1 To (((endDate - startDate + 1) + ROWGAP) * LOOPCOUNT) - ROWGAP, 1 To 1)
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = 1 To LOOPCOUNT
            For j = startDate To endDate
                rowCounter = rowCounter + 1
                outputArr(rowCounter, 1) = j
            Next j
            rowCounter = rowCounter + ROWGAP '<== Add gap
        Next i
        .Cells(1, 1).Resize(UBound(outputArr), UBound(outputArr, 2)) = outputArr 'This is only with one dimensional
        .Columns("A").NumberFormat = "m/d/yyyy"
    End With
    Application.ScreenUpdating = True
End Sub

tl;dr;

The principle is basically that you want an outer loop that increments from 1 to 54. Then an inner loop that increments from start date to end date. I treat date as a Long and simply add one to the startDate until I reach the endDate in the inner loop. For i = 1 To LOOPCOUNT is doing the repeat work... here you could be using your copy paste. I increment the rowCounter variable by 5 before the next repeat to leave some blank rows between repeats.

The first version writes to the sheet for every row with .Cells(rowCounter, 1) = j . That is an expensive operation "touching" the sheet each time. The second version does the same process but doesn't write to the sheet until the very end. Instead, it writes to an array. This is much faster as is all done in memory (no going to disk).

I know how many rows I will have in the array because I know how many times I am repeating the entire process (54), the number of days from startDate and endDate (8) and the number of padding rows I am adding (5). So I can size my array to write to with ReDim outputArr(1 To (((endDate - startDate + 1) + ROWGAP) * LOOPCOUNT) - ROWGAP, 1 To 1). I don't need 5 rows padding on the 54th loop so I remove these from the total row count.

For understanding working with arrays and data in the worksheet the article VBA Arrays And Worksheet Ranges is worth a read, a long with the more general VBA Arrays