Announcement

Collapse
No announcement yet.

VBA: Paste Special (Values)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA: Paste Special (Values)

    Hi,

    How do I incorporate the paste special (using values) feature into the following code?

    Range("A1:A2").Copy Destination:=Range("C3")

    Thank you for any help!

    Kim

  • #2
    Here I have written three procedure hop[e they help, chose which is for you, a bit more code but gets around all the issues you have and is abetter way to program for your task, IMO [in my opinion]

    Rdgs

    Jack


    Code:
    Sub BestVersion()
    ' Jack in the UK
    ' www.excel-it.com
    
    '//Range("A1:A2").Copy Destination:=Range("C3")
    '
    Dim rSource As Excel.Range
    Dim rDestination As Excel.Range
    Set rSource = ActiveSheet.Range("a1:a2")
    Set rDestination = ActiveSheet.Range("c3")
    
    rSource.Copy
    rDestination.Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
        
    Range("A1").Select
        
    Application.CutCopyMode = False
    
    valKill:
    Set rSource = Nothing
    Set rDestination = Nothing
    
    Exit Sub
    
    End Sub
    Code:
    Sub LongHandVersion()
    ' Jack in the UK
    ' www.excel-it.com
    
    '//Range("A1:A2").Copy Destination:=Range("C3")
    '
    Range("A1:A2").Select
    
    Selection.Copy
    Range("C3").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("A1").Select
    
    Application.CutCopyMode = False
    
    End Sub
    Code:
    Sub LongHandVersion_Better()
    ' Jack in the UK
    ' www.excel-it.com
    
    '//Range("A1:A2").Copy Destination:=Range("C3")
    '
    Range("A1:A2").Copy
    Range("C3").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues
        
    Range("A1").Select
    
    Application.CutCopyMode = False
    
    End Sub

    Comment


    • #3
      Re: VBA: Paste Special (Values)

      Hello Everbody
      I have assigned a task in which there is one master sheet which uses the msgbox option to ask that which slave worksheet you want to copy then when we select the other sheet then it must copy the value and there is another restriction which is that it must copy the value from the slave sheet when the cell in the slave sheet is green, And also offcourse there will e the formulas in the other sheet so in the master sheet there must be the PasteSpecial command to be used.

      I hope i will get the relevent answer.

      Comment


      • #4
        Re: VBA: Paste Special (Values)

        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?

        Code:
        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
        Last edited by Reafidy; May 12th, 2012, 07:18.

        Comment


        • #5
          Re: VBA: Paste Special (Values)

          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.

          Code:
          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


          Originally posted by Matt Heldman View Post
          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
          Last edited by AAE; May 12th, 2012, 21:20. Reason: remove indent tags from code

          Comment


          • #6
            Re: VBA: Paste Special (Values)

            Matt,

            Please use code tags for all code. I have added them for you this time.
            Reafidy

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment

            Working...
            X