How to add new row each day automatically below current data.

[Hmill]

I wrote this VBA code to fill in missing data up to current date. The problem is that is fills it in above my current data, not below which messes up everything. How can I fix this?

This is table.

VBA Code

 

[Joe4]

Is that the before or after image?
Can you show us what the data looks like BEFORE you run any code on it, and then what you want it look like AFTERWARDS?

 

[Hmill]

Thats the before. After I run it the dates fill in new rows above my start point and deletes the labels, etc. Start date was 3/16/2022.
This is what it looks like after.

From this code:

 

[Joe4]

OK, the insert happens BEFORE the selected row, so you want to use:

Rows(2).Insert

not

Rows(1).Insert

Also, you can copy all columns at once. Try this code:

Private Sub Workbook_Open()

    Dim lastRow As Long
    Dim lastDate As Date
    
'   Go to sheet with prices
    Sheets("WO Completed by Lab-Daily").Activate

'   Find last row
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Exit if last date >= current date
    If Cells(lastRow, "A") >= Date Then Exit Sub
    
'   Loop to add in new rows
    lastDate = Cells(lastRow, "A")
    Do Until lastDate = Date
        Rows(2).Insert
        lastDate = lastDate + 1
        Range("A2") = lastDate
        Range("B3:G3").Copy Range("B2")
    Loop
    
End Sub

 

[Hmill]

What does changing the number inside the Rows().Insert do?

 

[Joe4]

What does changing the number inside the Rows().Insert do?

It tells it what Row to insert the new row before.
So "Rows(2).Insert" says to insert one row before row 2.

 

[Hmill]

That code still causes it to fill upwards. I need it to descend if possible. Like insert the following date on the row below the start date and so forth.
This was the output for you code. It basically does the same thing.

 

[Joe4]

OK, note that I asked you to post what you WANTED it to look like, and that was the image you posted, so that is I wrote the code to do.
If you want it to go down, and there are just blank rows below your last row, there is no reason to insert any rows, just use the existing blank rows that are there.

So then this should work:

Private Sub Workbook_Open()

    Dim lastRow As Long
    Dim lastDate As Date
    
'   Go to sheet with prices
    Sheets("WO Completed by Lab-Daily").Activate

'   Find last row
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Exit if last date >= current date
    If Cells(lastRow, "A") >= Date Then Exit Sub
    
'   Loop to add in new rows
    lastDate = Cells(lastRow, "A")
    Do Until lastDate = Date
        lastDate = lastDate + 1
        lastRow = lastRow + 1
        Cells(lastRow, "A") = lastDate
        Range("B2:G2").Copy Cells(lastRow, "B")
    Loop
    
End Sub

 

[Hmill]

I'm sorry, I should have been more clear in the first post. This worked perfectly though. Thank you.

Now will it do that automatically every time I open the document. I would like for it to update to the current date whenever the file gets opened.

 

[Joe4]

Now will it do that automatically every time I open the document. I would like for it to update to the current date whenever the file gets opened.

"Workbook_Open" event procedures are already meant to do exactly that - run automatically whenever the workbook is opened.

In order for that to work, two things have to be done:
1. The code MUST be placed in the "ThisWorkbook" module in the VB Editor. If you place it in any other VBA module, it will NOT run automatically.
2. You MUST enable VBA/Macros to run on the workbook.