Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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


    VB:
    Sub BestVersion() 
         ' Jack in the UK
         ' [url]www.excel-it.com[/url]
         
         '//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 
    
    
    VB:
    Sub LongHandVersion() 
         ' Jack in the UK
         ' [url]www.excel-it.com[/url]
         
         '//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 
    
    
    VB:
    Sub LongHandVersion_Better() 
         ' Jack in the UK
         ' [url]www.excel-it.com[/url]
         
         '//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?

    VB:
    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 07: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.

    VB:
    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 21: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,923

    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 5 users browsing this thread. (0 members and 5 guests)

Possible Answers

  1. Paste Special Values
    By junll in forum EXCEL HELP
    Replies: 8
    Last Post: April 24th, 2007, 11:04
  2. Paste Special Values
    By ZeroMan001 in forum EXCEL HELP
    Replies: 1
    Last Post: January 17th, 2007, 10:38
  3. paste special values
    By ZeroMan001 in forum EXCEL HELP
    Replies: 3
    Last Post: February 7th, 2006, 06:44
  4. paste Special Values.
    By countryfan_nt in forum EXCEL HELP
    Replies: 3
    Last Post: September 17th, 2005, 19:40
  5. Avoiding Paste Special Values
    By steveorg in forum EXCEL HELP
    Replies: 3
    Last Post: March 10th, 2005, 08: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