paste special in this vba code?

M

Matt Heldman

Joined
May 11, 2012
Messages
2
Reaction score
0
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
 
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. :thumb:

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
 
Back
Top