Macro to Copy paste Value to cell adjacent to it

[Mutzie]

Hello All,
I have a spreadsheet with 3 columns. What I am struggling to achieve is how to copy paste value from the column “Duration Limit” to “Duration Report” when the “Status” column’s data validation list is changed to Closed.
The reason it has to be copy paste values is that the Duration limit cells are referenced from another worksheet and changes values every 1 second so I was hoping that as soon as the status is changed to Closed, the last value will be copy pasted to Duration Report per individual line.

[/IMG]

Thank you so much!

 

[Logit]

.
If a formula is acceptable .. paste this in C2 and drag down the column :

=IF(B2="CLOSED",A2,"")

If you are looking for a VBA code macro please advise.

 

[Mutzie]

Hello Logit, thank you for assisting with my question. I think a VBA code macro is needed since the values copied is still referenced to the reference sheet. The values appearing in the duration limit is changing every second so it has to be a copy paste value command so only the raw value is pasted and does not include the formula or references.

It's sort of a time stamp tracker where the duration limit display running time in hours;minutes;seconds so the time at the time the status was changed to closed has to be recorded.

 

[Logit]

.
Paste in the Sheet Level Module :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    
    Dim ws As Worksheet
    Dim i As Integer
    Dim lastRow As Integer


    On Error Resume Next
    Set ws = Sheets("Sheet1")               'You could also use Excel.ActiveSheet _
                                            if you always want this to run on the current sheet.
    'Get the last row
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
        
    Application.ScreenUpdating = False


    For i = 2 To lastRow
            If ws.Cells(i, 2).Value = "CLOSED" Then
               ws.Cells(i, 3).Value = ws.Cells(i, 1).Value
            End If
        
    Next i


    Application.ScreenUpdating = True
End Sub

Change sheet name to match.

Download workbook : https://www.amazon.com/clouddrive/share/d6sk5m438aXh5Wdeia01qq016zBS1azj5EyZBGff78L