Forum Discussion

Oliver2393's avatar
Oliver2393
Copper Contributor
Jan 01, 2020

How to add date every day

Hi, I would like to add the current date every day, how could I do that? Enter a date every date on another line, from line A1

excel
Macros and VBA

3 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jan 02, 2020

    Oliver2393

     

    Hi,

     

    You need this code:

     

     

    Private Sub Workbook_Open()
'This procedure will run each time you open the workbook
'By Haytham Amairah
'Last Updated: 1/2/2020

    'Specify the required worksheet name in double quotes
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    
    'Get the last row number filled with a value in Column A
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Check if the last entered date is the same as the current date, if so, exit
    'You need this check to see if you close the workbook then open it on the same day
    'so that the code does not enter the same date again in a new row.
    If ws.Cells(lastRow, 1).Value = Date Then Exit Sub
    
    'Fill a new row in Column A with the current date
    If IsEmpty(Cells(lastRow, 1)) Then
        ws.Cells(lastRow, 1).Value = Date
    Else
        ws.Cells(lastRow, 1).Offset(1, 0).Value = Date
    End If
End Sub

     

     

     

    You have to save this code in ThisWorkbook code module which found in the VBA editor. Please check out this video to learn https://youtu.be/VJWBOuNXHB4?t=425.

     

    Hope that helps

    • Oliver2393's avatar
      Oliver2393
      Copper Contributor
      Jan 02, 2020

      Hi, thank you for reply, if I would've wanted to add the date to every sheet, what should I change in the code?

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Jan 02, 2020

        Oliver2393

         

        In this case, you need to update the code as below by using For...Next...Loop statement to loop through each worksheet and apply the code to it.

        Private Sub Workbook_Open()
'This procedure will run each time you open the workbook
'Applied to all worksheets
'By Haytham Amairah
'Last Updated: 1/2/2020

    For Each Worksheet In Worksheets
        'Get the last row number filled with a value in Column A
        Dim lastRow As Long
        lastRow = Worksheet.Cells(Rows.Count, 1).End(xlUp).Row
    
        'Check if the last entered date is the same as the current date, if so, exit
        'You need this check to see if you close the workbook then open it on the same day
        'so that the code does not enter the same date again in a new row.
        If Worksheet.Cells(lastRow, 1).Value = Date Then GoTo NextSheet
        
        'Fill a new row in Column A with the current date
        If IsEmpty(Worksheet.Cells(lastRow, 1)) Then
            Worksheet.Cells(lastRow, 1).Value = Date
        Else
            Worksheet.Cells(lastRow, 1).Offset(1, 0).Value = Date
        End If
        
NextSheet:
    Next Worksheet
End Sub

         

        Regards

Resources