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.
Similar Threads
  1. Lawrence M. Seldin, CMC, CPC

    Paste Link is greyed out during Paste Special

    Lawrence M. Seldin, CMC, CPC, May 10, 2004, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    658
    Lawrence M. Seldin, CMC, CPC
    May 10, 2004
  2. Sharon Perez

    Dynamic Copy/Paste Special Formulas/Paste Special Values

    Sharon Perez, Aug 6, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    945
    SidBord
    Aug 7, 2004
  3. Lourens Pentz

    Cut and Paste using Macro gives paste special method error

    Lourens Pentz, Nov 10, 2004, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    362
    Dave Peterson
    Nov 16, 2004
  4. Guest
    Replies:
    0
    Views:
    205
    Guest
    Nov 19, 2004
  5. usmc-r70

    Special Copy, Paste Special macro

    usmc-r70, Jul 2, 2009, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    539
    usmc-r70
    Jul 2, 2009
Loading...

Share This Page