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:


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.


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("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.

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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext