Excel – How to Paste only Values with VBA

--> (Word)	--> (PDF)	--> (Epub)
This article has been published [fromdate]

SCENARIO

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

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

SOLUTION

From: Jack in the UK (OZGRID FORUM)

Here I have written three procedure hope they help, chose which is for you, a bit more code but gets around all the issues you have and is a better way to program for your task.

[tab:First Method] First Method

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 

[tab:Second Method]

Second Method

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 

[tab:Third Method]

Third Method

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 

[tab:END]

SOURCE	LINK (Ozgrid.com)	LANGUAGE	ENGLISH