Hello everyone,
I have been put in charge of preparing an Excel book for work (despite very limited ability) and have managed to automate a great deal of the process. The book has about 34 sheets, each requiring one row added per day. I have done some perusing on this forum, and found some stuff that has been helpful, but I'm still missing the mark. When I run this code, it starts at 1901 rather than yesterday (which is when the last entries were done).
The code I'm using to add the extra line is:
I adapted it from this thread: How to add new row each day automatically?
Using this video to try to flip it to add rows to the end:
My intent was to get one sheet working before trying to integrate the next sheet code, but I'm not sure how to integrate that into the loop either. The code I found to select next sheet is:
I found this on: VBA Code To Select Next & Previous Spreadsheet Tabs — The Spreadsheet Guru
Could somebody please help me get this working? I'm not sure where I am going wrong.
I have been put in charge of preparing an Excel book for work (despite very limited ability) and have managed to automate a great deal of the process. The book has about 34 sheets, each requiring one row added per day. I have done some perusing on this forum, and found some stuff that has been helpful, but I'm still missing the mark. When I run this code, it starts at 1901 rather than yesterday (which is when the last entries were done).
The code I'm using to add the extra line is:
VBA Code:
Private Sub Workbook_Open()
Dim lastDate As Date
' Go to the first sheet
Sheets("Aggregate - Europe").Activate
' Check the first value
lastDate = Range("A" & Rows.Count).End(xlUp)
If lastDate < Date Then
Do Until lastDate = Date
lastDate = Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.EntireRow.Insert
lastDate = lastDate + 1
Range("A" & Rows.Count).End(xlUp) = lastDate
Loop
End If
End Sub
I adapted it from this thread: How to add new row each day automatically?
Using this video to try to flip it to add rows to the end:
My intent was to get one sheet working before trying to integrate the next sheet code, but I'm not sure how to integrate that into the loop either. The code I found to select next sheet is:
VBA Code:
Sub SelectNextSheet()
Dim sht As Worksheet
'Store currently selected sheet
Set sht = ActiveSheet
'Loop to next sheet until visible one is found
On Error Resume Next
Do While sht.Next.Visible <> xlSheetVisible
If Err <> 0 Then Exit Do
Set sht = sht.Next
Loop
'Activate/Select next sheet
sht.Next.Activate
On Error GoTo 0
End Sub
I found this on: VBA Code To Select Next & Previous Spreadsheet Tabs — The Spreadsheet Guru
Could somebody please help me get this working? I'm not sure where I am going wrong.