paste special in this vba code?

Discussion in 'Microsoft Excel Programming' started by Matt Heldman, May 11, 2012.

  1. Matt Heldman

    Matt Heldman

    Joined:
    May 11, 2012
    Messages:
    2
    Likes Received:
    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
     
    Matt Heldman, May 11, 2012
    #1
    1. Advertisements

  2. Matt Heldman

    Matt Heldman

    Joined:
    May 11, 2012
    Messages:
    2
    Likes Received:
    0
    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
     
    Matt Heldman, May 11, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page