Copy and Paste Today's Date in Excel

Question

I am currently trying to create a function that auto-populates the current date in a cell only if the cell adjacent to it contains the text Open.

Similarly, I also would like the function to do the same if the status is Closed.

The main challenge with this code is that I do not want the date to change by using functions like =TODAY() or =NOW(), bht instead I would like these dates to be permanent once populated.

Here is an image of the table I am hoping to have this functionality work on.

In terms of code, here is what I was able to muster up:

Function add_todays_date() 

    If Sheets("Sheet2").Range("C5") = "Open" Then 
        Sheets("Sheet3").Range("G4").Copy 
        ActiveWorkbook.Sheets("Sheet2").Range("D5").PasteSpecial Paste:=xlValues

You can do something like Cells(1, 1) = Format(Now(), "dd/mm/yyyy") but if you run the code it will overwrite with current date. If you want it to be permanant do an Isempty test on the cell first. — QHarr, Jan 29 '18 at 18:16

QHarr · Accepted Answer · 2018-01-29T18:27:42.197

1

You can combine with an IsEmpty test to avoid overwriting cells you have already looked at and populated.

To demonstrate the principle:

Option Explicit

Sub test()

    With ActiveSheet

        If IsEmpty(.Cells(1, 2)) And .Cells(1, 1) = "Open" Then

            .Cells(1, 2) = Format(Now(), "dd/mm/yyyy")

        End If

    End With

End Sub

Or in a loop construct:

Option Explicit

Sub test()

    Dim currCell As Range

    For Each currCell In ActiveSheet.Range("B1:B10") 'Adjust as required

        If IsEmpty(currCell) And currCell.Offset(0, -1) = "Open" Then

            currCell = Format(Now(), "dd/mm/yyyy")

        End If

    Next currCell

End Sub

edited Jan 29 '18 at 18:27

answered Jan 29 '18 at 18:20

QHarr

83,427
12
54
101

Great it works! I have one more quick follow up question, is there anyway to have this do the auto population without having to run the sub each time? – Oilprogramming Jan 29 '18 at 18:46
Yes, you have alot of options, look at this example [link](https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) – warner_sc Jan 29 '18 at 18:57
1

You could tie it to a worksheet change event for example. You need to decide what the appropriate trigger would be and this may also depend on how frequently you want to add in the dates. – QHarr Jan 29 '18 at 19:17

score 1 · Answer 2 · answered Jan 29 '18 at 18:32

I dont think you want a function for this, they work like Excel worksheet functions, they might update the next time you open the workbook or refresh the worksheet.

    Option Explicit

    Public Function add_todays_date()

    Dim selected_range As Range
    Set selected_range = Selection

    With selected_range.Offset(0, -1)

        If .Value = "Open" Or .Value = "Closed" Then
            add_todays_date = Format(Now, "dd/mm/yy")
        End If

    End With


    End Function

@Qharr answer is what i would do, you can even create a shortcut to execute the subroutine faster than typing a function..

Thanks for pointing out! I forgot to add the comment about recalc and hence sub. — QHarr, Jan 29 '18 at 18:38

Copy and Paste Today's Date in Excel

2 Answers2