Posting a date based on an event

Hello Carl,

I think you can fix it by adding the following command. Can you try it and let me know?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetSheet As Worksheet
    Dim dateColumn As Range
    
    ' Set target sheet where the date should be posted
    Set targetSheet = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to the actual name

    ' Define the range where the paste event happens
    Set dateColumn = Me.Range("B:B") ' Assuming you are pasting in column B on Sheet1

    ' Check if the pasted cells are in the specified range
    If Not Intersect(Target, dateColumn) Is Nothing Then
        ' Find the first empty cell in the target column on the target sheet
        Dim emptyRow As Long
        emptyRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1 ' Adjust "A" as needed

        ' Post the current date in the target sheet
        targetSheet.Cells(emptyRow, "A").Value = Date ' Adjust "A" to the desired column for the date
    End If
End Sub

Best Regards,

Ali Koc

Try this:

1: Set Up Your Sheets

1. Sheet 1: This sheet contains your roster and the random selection of people.
2. Sheet 2: This sheet will display the selection date based on the event happening on Sheet 1.

2: Create a Trigger Event

You need to create a trigger event on Sheet 1 that will update the date on Sheet 2. You can use the IF and VLOOKUP functions to achieve this.

3: Write the Formula

On Sheet 2, in the cell where you want to display the selection date, use the following formula:

excel

=IF(VLOOKUP(A2,Sheet1!A:A,1,FALSE)<> "", TODAY(), "")

4: Copy the Formula

Copy the formula to other cells in the same column on Sheet 2 where you want to display the selection date.

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.