Posting a date based on an event

To achieve this, you can use a combination of formulas like IF and TODAY to post a date based on an event, but the main challenge is that formulas themselves cannot detect events like copying and pasting. However, you can use a VBA macro to automatically update a date when a specific event occurs, such as pasting values into certain cells. Here’s how you can approach this:

Option 1: Using VBA for Event Tracking

 1. Open the VBA Editor:

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

 2. Create a Worksheet Change Event:

• In the Project Explorer, double-click on the sheet where you want to detect the paste event (e.g., Sheet1).

 3. Paste the Following Code:

Vba Code is untested backup your file

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchRange As Range
    Dim cell As Range

    ' Set the range to monitor (e.g., cells where you paste values)
    Set watchRange = Me.Range("B2:B100") ' Adjust as needed

    ' Check if the change was within the monitored range
    If Not Intersect(Target, watchRange) Is Nothing Then
        Application.EnableEvents = False
        
        ' Loop through each changed cell in the watch range
        For Each cell In Target
            If Not IsEmpty(cell.Value) Then
                ' Post the current date in another sheet (e.g., Sheet2)
                Worksheets("Sheet2").Cells(cell.Row, "C").Value = Date ' Adjust column as needed
            End If
        Next cell
        
        Application.EnableEvents = True
    End If
End Sub

4. Adjust the Code:

• Change watchRange to match the cells in which you paste values.
• Modify Worksheets("Sheet2").Cells(cell.Row, "C") to specify where the date should be posted on the other sheet.

Explanation:

• watchRange: Specifies the range of cells where pasting values triggers the macro.
• Date Posting: The code posts the current date (Date function) into the specified cell on Sheet2 whenever a value is pasted into the monitored range.
• Application.EnableEvents = False: Prevents the macro from triggering itself when updating cells.

Option 2: Formula-Based Workaround (Limited)

Formulas alone cannot detect a paste event, but you can try setting up a helper cell and using conditional logic:

 1. Helper Column:

• On Sheet1, set up a helper column that changes based on specific criteria.

 2. TODAY() Formula:

• On Sheet2, use an IF formula to post a date if a condition is met:

=IF(Sheet1!B2<>"", TODAY(), "")

• This will show today’s date when a cell in Sheet1 has a value, but the date will update every day. This method is not persistent and only works as a temporary solution.

Limitations:

• VBA Approach: Requires macros to be enabled and might need user permission to run.
• Formula Approach: Not ideal for event detection as it will continuously change the date.

The VBA solution is the most effective way to detect and respond to pasting values in cells and to post a date in another sheet accordingly.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.