Copying

Excel > Charts > VBA Code > Copying
Excel > Illustrations > VBA Code > Copying
Word > Paragraphs > VBA Code

Copying and Pasting a single value

The most intuitive way to copy data from one cell to another would be:

Range("A2").Select 
Selection.Copy 
Range("D2").Select 
ActiveSheet.Paste 

One of the most important aspects of VBA programming is remembering that you do not have to actually select the object you want to manipulate.
The code could therefore be changed to the following.

Range("A2").Copy 
Range("D2").Select 
ActiveSheet.Paste 

There are always several ways to accomplish the same thing in VBA.
The code could be reduced to two lines using the Paste Special method.

Range("A2").Copy 
Range("D2").PasteSpecial Paste:=xlValues 

It is actually possible to condense this action into a single line.
This is possible because the Copy method can take an optional argument that can represent the range to be copied to. The next two lines are equivalent.

Range("A2").Copy Destination:=Range("D2") 
Range("A2").Copy Range("D2") 

This copies the contents of cell "D2" and places them into the active cell.

Range("D2").Copy Destination:=ActiveCell    

Copying Data between Worksheets

It is possible to copy data between worksheets.
The can be done by including a reference to a particular worksheet. If no worksheet is specified then the active worksheet is used.

Worksheets("Sheet1").Range("A2").Copy Worksheets("Sheet2").Range("D2") 

Notice that you do not have to activate the necessary worksheets.
The following line of code copies the whole block of data to a worksheet Sheet2.

Range("A2").CurrentRegion.Copy Sheets("Sheet2").Range("D2") 

Copying Data between Workbooks

It is possible to copy data between worksheets.
The can be done by including a reference to a particular worksheet. If no worksheet is specified then the active worksheet is used.

Workbooks("Wbk1.xls".Worksheets("Sheet1").Range("A2").Copy Workbooks("Wbk2.xls").Worksheets("Sheet2").Range("D2") 

Long lines of code can sometimes be difficult to understand.
Another way to perform the same task is to use variables to store the necessary range objects.

Dim rgeCopyRange As Range 
Dim rgeToRange As Range 

Set rgeCopyRange = Workbooks("Wbk1.xls").Worksheets("Sheet1").Range("A2") 
Set rgeToRange = Workbooks("Wbk2.xls").Worksheets("Sheet2").Range("D2") 
rgeCopyRange.Copy rgeToRange 

Cancelling the Copy Mode

When you copy (or Cut) a range of cells a black dotted line appears around the area to help identify it.
This can be removed by switching the CutCopy property to False.

Application.CutCopyMode = xlCutCopyMode.False 

If you do not change this property back to false then the cell range that was last copied will remain flashing on the screen.

Range("A2").Copy 
Range("D2").Select 
ActiveSheet.Paste 
Application.CutCopyMode = xlCutCopyMode.False 

You do not need to reset this property if you use a single line statement to copy and paste.

Range("A2").Copy Range("D2") 

Copying Ranges as Pictures

to copy the selected worksheet range as a picture

Selection.CopyPicture Appearance:=xlPictureAppearance.xlScreen, Format:=xlCopyPictureFormat.xlPicture 

Freeze and unfreeze references when copying

Public arFormulas() As Variant 

Public Sub Freeze() 
    arFormulas = Selection.Formula 
End Sub 

Public Sub UnFreeze() 
    If (Not Not arFormulas) = 0 Then 
        Call MsgBox ("Error: No formulas were copied. Please use the Freeze routine first.") 
        Exit Sub 
    End If 

    Selection.Resize(UBound(arFormulas, 1), UBound(arFormulas, 2)).Value = arFormulas 
End Sub