Need code for copy paste values after change event

digladio · February 14th, 2011, 02:01 AM

Hello,
I am looking for simple code. I tried myself but can't seem to get it to work.

In the A column, user will be entering values. In the B column, I have a formula that will enter the system time ("now") only when a value is entered in A.

I need macro code so that once a value is entered in column A, the values from column B are pasted special (as values) into column C.

I think I need to use Worksheet_change and the following code line.

Code:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Also, there is no way to do this simply with a formula and without macros is there?

davesexcel · March 11th, 2011, 04:52 AM

Try this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Target.Offset(0, 2) = Target.Offset(0, 1)
End Sub

emphasizebpo · March 11th, 2011, 06:11 AM

Is the code working properly, Please Give Us an reply http://www.emphasizebpo.com

mtranchi · March 11th, 2011, 07:16 AM

I'm guessing your column B has an If() function that displays Now() only if there's a value in column A. You can skip using that If() entirely and just enter the value in column C (or B for that matter). The following code puts the current date/time in column b if something was entered in column a, and also deletes the date/time in column b if one or more rows are deleted in column a.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 1 And Target.Column = 1 Then 'something has been done in column A
    If Target.Rows.Count = 1 Then  'a value has been entered/changed/deleted in one row of column A
        If Len(Target) > 0 Then 'something has been entered or changed in column A
            Target.Offset(0, 1) = Now
        Else 'a value has been deleted from one row, delete DateTime for that row
            Target.Offset(0, 1).Clear
        End If
    Else 'a value has been deleted from multiple rows, so delete the DateTimes for those rows
        Target.Offset(0, 1).Clear
    End If
End If

End Sub