Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA: Paste Special (Values)

  1. #1
    Join Date
    18th July 2004
    Posts
    33

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th February 2012
    Posts
    19

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    10th May 2012
    Posts
    2

    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 at 08:18.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th May 2012
    Posts
    2

    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


    Quote 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 at 22:20. Reason: remove indent tags from code

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    Re: VBA: Paste Special (Values)

    Matt,

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Paste Special Values
    By junll in forum EXCEL HELP
    Replies: 8
    Last Post: April 24th, 2007, 12:04
  2. Paste Special Values
    By ZeroMan001 in forum EXCEL HELP
    Replies: 1
    Last Post: January 17th, 2007, 11:38
  3. paste special values
    By ZeroMan001 in forum EXCEL HELP
    Replies: 3
    Last Post: February 7th, 2006, 07:44
  4. paste Special Values.
    By countryfan_nt in forum EXCEL HELP
    Replies: 3
    Last Post: September 17th, 2005, 20:40
  5. Avoiding Paste Special Values
    By steveorg in forum EXCEL HELP
    Replies: 3
    Last Post: March 10th, 2005, 09:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno