paste special in this vba code?

[Matt Heldman]

This works great except for one thing. It duplicates conditional formating every time it copies. Is there a way to make it "paste special values" and leave the rest alone?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rFrom As Range
​

If Target.Count = 1 Then

With Target.Worksheet

Set rFrom = .Range("A2:C280")
If Not Intersect(Target, rFrom) Is Nothing Then

Application.EnableEvents = False
'Include next line Just in Case something happens
' You don't want to leave EnableEvents off
On Error Resume Next
rFrom.Copy Worksheets("RS Scale").Range("A2:C280")
If Err.Number <> 0 Then
MsgBox "Error Occurred"
End If
Application.EnableEvents = True
​

End If
​

End With
​

End If
​

End Sub

 

[Matt Heldman]

I got this to work. It's cleaner and faster. But if I highlight and clear multiple cells or press delete it does not erase it from the second sheet. I can live with that but if anyone has any tips to do make deleting multiple cells work, that would be great.

This version goes in the CS Scale sheet and a reverse sheet order goes in the RS Scale sheet. Works great.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rFrom As Range
If Target.Count = 1 Then

With Target.Worksheet
Worksheets("RS Scale").Range("A2:C500").Value = Worksheets("CS Scale").Range("A2:C500").Value
End With
​

End If
​

End Sub