copy and paste special value only on IF function

[jeff]

Hi,

Here is some code that will run whenever a change is
made on your sheet. Adjust it to move what and when
you need.

jeff

Private Sub Worksheet_Change(ByVal Target As Range)
'exit if change is not in col c-e
If Intersect(Target, Me.Range("c:e")) Is Nothing Then
Exit Sub
' if value is nonzero simple copy over
'If Target.Value <> 0 Then Target.Offset(0, 4) =
Target.Value
' do a paste special on target only
If Target.Value <> 0 Then
Target.Select
Selection.Copy
Target.Offset(0, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' another way to copy and paste (but not special)
'If Target.Value <> 0 Then
' Target.Copy Destination:=Target.Offset(0, 4)
End If
' use these if you want to copy cols D & E whenever C is
changed
'If Target.Offset(0, 1) <> 0 Then Target.Offset(0, 4) =
Target.Offset(0, 1).Value
'If Target.Offset(0, 2) <> 0 Then Target.Offset(0, 3) =
Target.Offset(0, 2).Value
End Sub

 

[jeff]

Hi,

Sorry to respond so late - I've been away from my desk.

Any part of the macro code starting with an apostrophe
is a comment - I (perhaps unfortunately) included some
extra lines which are comments, but show syntax of other
ways to do the pasting.

To get this to run (and be sure to try this on a test
worksheet if possible), on your active worksheet, right
click on the tab (e.g., "Sheet1"), select "View code";
and paste the code in. Since it's a
"Private Sub Worksheet_Change" sub, whenever the worksheet
is changed, it will fire and process the code below.

Note when you paste it in, you may get some lines
in red -meaning an error of some kind; for example

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _

These should be on one line (the space+underscore
indicates a continuation); you may have to fix a few
lines due to how this posting is broken up.
If you have problems/post back.

jeff

-----Original Message-----
Jeff

Thanks for your response. I am sorry but I am somewhat of a novice at this.
Am I correct in thinking that the part

starting 'another way to copy and