Copying, Cutting, and Pasting Ranges of Cells

Copying, Cutting, and Pasting Ranges of Cells

If you wish to relocate or copy a range of cells, your best bet is to use the clipboard, just as in using Excel manually. The following methods are designed for data transfer to and from the clipboard.

Copy: This method copies the range of cells specified as object to the clipboard. If the method is used with an optional parameter, then the data are not copied to the clipboard, but directly to the given range.

Cut: This method functions like Copy, but the original data are deleted. If a range is given with the optional parameter "destination," then the cells will be shifted to that location. For this reason there is no method specifically for shifting cells.

Paste: This method inserts data from the clipboard. A worksheet must be given as object. If the destination is not given in an optional parameter, then the current selection in the worksheet will be the destination.

PasteSpecial: This method enables more complex editing with the command EDIT|PASTE SPECIAL, such as the insertion of values (instead of formulas) or carrying out calculations. This method recognizes numerous optional parameters, which are described in the on-line help. In particular, with the help of these parameters you can shift to the right or below those cells that were overwritten by the insertion.

Two properties of the object Application give additional information about the current contents of the clipboard and the current copy or cut mode:

CutCopyMode: This property tells whether Excel is currently in copy or cut mode. Possible values are False, xlCut, and xlCopy. With a specification of False an operation of cutting or copying that has already begun can be interrupted. With this the blinking frame around the copied or cut data disappears.

ClipboardFormats: This enumeration property tells which formats are exhibited by the data in the clipboard. This property is organized as a field, since the clipboard can contain data in several formats simultaneously. Possible formats are xlClipboardFormatText and xlClipboardFormatBitmap (see the on-line help).

Tip / Starting with Office 2000, Excel, Word, and the like possess not merely one clipboard, but twelve. In other words, the last twelve cut or copied data are in temporary storage and can be restored as needed. For this you need to make the toolbar "Clipboard" visible.
However, this new feature is not accessible to VBA programmers. The commands described in this section are valid only for the last piece of data added to the clipboard. The up to eleven remaining clipboard items cannot be accessed by code.

Copying a Range of Cells into Another Sheet

The following instructions copy the data of the current region in which the cell pointer is located from table 1 to table 2. With SpecialCells(xlVisible) only visible data are copied. This restriction makes sense, for example, in database applications in which only the filtered data are to be transferred. If you simply wish to transfer the selected data, then the instruction Selection.Copy suffices.

Note that when Paste is invoked, although the active sheet is specified as object, the data beginning with cell A1 are copied into table 2.

' copy visible data to the clipboard

Selection.CurrentRegion.SpecialCells(xlVisible).Copy

' insert data beginning with A1 into table 2

ActiveSheet.PasteRange("Table2!A1")

' cancel copy mode (blinking border)

Application.CutCopyMode = False