PROGRAMMATICALLY CUT, COPY AND PASTE

PROGRAMMATICALLY CUT, COPY AND PASTE

COPYING AND PASTING

When copying a range from one sheet and pasting to another sheet, you do not need to activate or select the destination sheet. Also, you should always specify the destination range. Otherwise Excel assumes that you wish to paste to the current selection on the destination sheet.

Sub DoCopyExample1()

Dim srceRng As Range

Dim destRng As Range

'set range variables to refer to the range to be copied and the

'destination range. Note that the destination range is a single cell

Set srceRng = _

Workbooks("book1.xls").Sheets("sheet1").Range("A1:A10")

Set destRng = _

Workbooks("book1.xls").Sheets("sheet2").Range("A1")

'APPROACH 1

'copy the range

srceRng.Copy

'paste, specifying the destination sheet and range

Workbooks("book1.xls").Sheets("sheet2").Paste destRng

'APPROACH 2

'specify the destination sheet by referring to the parent of the range, and

'do a copy paste on one row

destRng.Parent.Paste destRng

'APPROACH 3 - THE SIMPLEST

'copy the source range and specify the destination range

srceRng.Copy destRng

End Sub

The following illustrates how to copy the same range on one sheet to another sheet:

Sub DoCopyExample3()

Dim szRange As String

szRange = "C3:K3"

Worksheets("Data").Range(szRange).Copy _

Destination:=Worksheets("Timesheet").Range(szRange)

End Sub

COPYING VALUES WITHOUT PASTING

PasteSpecial is a fairly difficult command to use. Often, the only way to figure it out is to record a macro that uses this command. If all you want is to copy the values in a range, there is a slightly easier way. If you specify code that looks like the following, and the ranges are the same size, then only values will be copied:

destinationCells.Value = SourceCells.Value

The following example illustrates this, and also provides a subroutine that you may want to make part of your library of must have routines:

Sub CopyValuesExample()

Dim rangeToCopy As Range

Dim destCell As Range

On Error Resume Next

With Application

'get the input range, exit if no input

Set rangeToCopy = .InputBox( _

"Select the range whose values will be copied", _

Default:=Selection.Address, Type:=8)

If rangeToCopy Is Nothing Then Exit Sub

'get the destination cell

Set destCell = .InputBox( _

"Select the destination", Type:=8)

If destCell Is Nothing Then Exit Sub

End With

On Error GoTo 0

'call the routine that copies the values

CopyCellValues rangeToCopy, destCell

End Sub

Sub CopyCellValues(ByVal SourceCells As Range, _

ByVal destCell As Range)

'make certain destination is a single cell

Set destCell = destCell.Cells(1)

'resize destination to the same size as the source range

With SourceCells

Set destCell = destCell.Resize _

(.Rows.Count, .Columns.Count)

End With

'set values to be the same

destCell.Value = SourceCells.Value

End Sub

Converting numbers that appear as text back to numbers

Every now and then Excel will treat numbers as text. This is most obvious when they appear left justified in a cell. And, clearing the cells format does not help. Or, you get multiple listings for the same number in a pivot table. This typically happens when importing data, but other events can cause it to happen. The following is a Visual Basic solution to this problem.

Dim R As Range

Set R = Selection

R.NumberFormat = "General" ' or whatever you want, but not "@"

R.Value = R.Value

The following is one way to convert these entries back to numeric entries

Dim c As Range

For Each c In Selection

c.Formula = c.Formula

Next c

The following is still another way to fix this problem:

Dim tempR As Range

'find a blank cell

Set tempR = Cells(ActiveSheet.UsedRange.Count + 1)

'format the selection as a number

Selection.NumberFormat = "0"

'copy tempR, which is a blank cell

tempR.Copy

'add tempR's value to the cells in the selection

Selection.PasteSpecial Paste:=xlValues, operation:=xlAdd

'clear the clipboard

Application.CutCopyMode = False

Still another approach you can use is:

Dim cell As Range

For Each cell In Selection

If Not IsEmpty(cell) Then cell.Value = cell.Value

COPYING CELLS, ROWS, AND COLUMNS

To copy a cell or a range of cells, the Copy method is used. The Copy command also has the option to specify the destination at the same time. This shortcut has obvious benefits. If a destination is not specified, then the range is copied to the clipboard. The syntax of the Copy method is:

range object.Copy

or range object.Copy destination:=range object

or range object.Copy range object

or range object.Copy(range object)

Examples of range objects that may be used with the Copy method are ActiveCell, Selection, ActiveCell.EntireRow, Selection.

EntireColum, and Range(). The range object must be a single area, as multiple areas do not work with the Copy method. Use Selection.Areas.Count to confirm that only one area has been selected.

The following statement copies the selection to the clipboard:

Selection.Copy

The following copies the range identified by an object variable

carSales.Copy

You do not have to be in the workbook that the object variable refers to. Visual Basic knows which sheet and book the object variable refers to.

The following copies a range in another workbook and pastes it to a different workbook, without leaving the current active workbook.

Workbooks("Prices.Xls").Sheets("1996").Range("prices").Copy _

WorkBooks("Update.Xls").Sheets("1997").Range("C4")

The following accomplishes the same task, but with object variables.

Set sourceBook = Workbooks("Prices.Xls")

Set sourceRange = sourceBook.Sheets("1996").Range("prices")

Set destBook = WorkBooks("Update.Xls")

Set destRange = destBook.Sheets("1997").Range("C4")

sourceRange.Copy destRange

Using object variables makes your statements easier to understand. And, you can use the object variables in other statements in your macro.

If a destination argument is specified (as in the second and third example above), it must be either a single cell or a multiple of the range being copied.

USING THE CUT METHOD

To cut a cell or a range of cells to the clipboard or direct to a destination, the Cut method is used. The Cut command also has the option to specify the destination like the Copy method. If a destination is not specified, then the range is cut to the clipboard. The syntax of the Cut method is:

range object.Cut

or range object.Cut destination:=range object

or range object.Cut range object

or range object.Cut(range object)

Examples of range objects that may be used with the Cut method are ActiveCell, Selection, ActiveCell.EntireRow, Selection.

EntireColum, and Range(). You can also specify ranges on other sheets and in other workbooks. The range object must be a single area

, as multiple areas do not work with the Cut method.

The following illustrates the use of this method to cut the range specified by object variable newDataToUse to the clipboard:

newDataToUse.Cut

This next statement cuts range B4:D7 on the active sheet and pastes it to the right four columns.

Range("B4:D7").Cut(ActiveCell.Offset(0,4))

This next statement cuts row 6 and pastes it into row 9.

Rows(6).Cut destination:=Rows(9)

If a destination range is specified, it must be either a single cell or a multiple of the range being cut.

PASTING CELLS, ROWS, AND COLUMNS

The Paste method pastes the contents of the clipboard into the sheet. You can use it without any options to paste at the active cell (the worksheet must be specified). Or, you may specify either a destination object or that the paste is a paste link to the copied range. The following are acceptable syntax:

worksheet object.Paste

or worksheet object.Paste destination:=range object

or worksheet object.Paste range object

or worksheet object.Paste(range object)

or worksheet object.Paste link:=True

A worksheet object is required whenever the Paste command is used. The destination range can be either a single cell or a range that is a multiple of the range being pasted. The following are examples of worksheet objects:

ActiveSheet

Sheets("Sheet 1")

Workbook("Mydata.xls").Sheets("January")

An object variable set to a worksheet

Using the Paste method without any arguments is the simplest format. First you issue the Copy or Cut command and then go to the destination range. The following illustrates this approach:

Selection.Copy

ActiveCell.Offset(-5, 0).Select

ActiveSheet.Paste

The above example copies the selection, selects a cell five cells to the left, and then pastes the copied selection. Please note that only a single area can be copied. If multiple areas are selected when you issue the Copy command, an error will occur.

The destination argument of the Paste method allows you to specify the destination range instead of first having to select it. This is the most efficient way to use the Paste method. For example, the following is the same as the above illustration except that the selection remains the copied cells. This saves you from having to move to the destination cells.

Selection.Copy

ActiveSheet.Paste destination:=ActiveCell.Offset(-5, 0)

Selection.Copy

ActiveSheet.Paste(ActiveCell.Offset(-5, 0))

or even better, do it without moving the active cell:

Selection.Copy ActiveCell.Offset(-5, 0)

If a destination range is specified, it can be on the same sheet, a different sheet, or in a different workbook. The following uses the Copy and Paste methods without first selecting the ranges:

Workbooks("Cars.Xls").Sheets("1996").Range("A1:B7").Copy

ActiveSheet.Paste _

Workbooks("book11").Sheets("sheet3").Range("a1")

The last form of the Paste method is used to do a paste link. To use, you first issue the Copy command and then select the destination range. You then specify True to the link argument. A paste link means that a link is established to the source cells and if they are changed, the pasted cells changes. However, you can not modified the pasted cells. The paste special command is a rather special command and should be used with caution.

PASTE SPECIAL - PASTE ONLY VALUES OR FORMATS

The PasteSpecial method pastes a range from the clipboard to the specified range and specifies what is to be pasted. For example, you can use the PasteSpecial method to paste just the values of a copied selection. The syntax of the PasteSpecial method is:

range object.PasteSpecial paste:=vb constant, _

operation:=vb constant, _

skipBlanks:=True or False, _

transpose:= True or False

Please note that if PasteSpecial is used on the active sheet, it changes the active cell. If used on a non-active sheet, the active cell on that sheet is not changed.

For example, the following copies a selection, and then pastes just the values of the copied cells into cell F5 on Sheet5 of the active workbook:

Selection.Copy

Sheets("Sheet5").Cells(5, 6) _

.PasteSpecial paste:=xlValues, _

operation:=xlNone, _

skipBlanks:=False, _

transpose:=False

Note that Sheet5 did not have to be selected in order for the above statements to work.

A range object is a range of cells such as ActiveCell, Range(), Rows(), Selection.EntireColumn or Selection and is required. The range object must be either a single cell or a multiple of the range being pasted. It can also refer to a range on another sheet or in another workbook.

All the arguments to the PasteSpecial method are optional. You can specify the argument values inside parentheses if you want. However, you can not specify the argument names if you use parentheses. For example,

ActiveCell.PasteSpecial(xlValues)

pastes just the values. Since the other argument values are not specified, their default values are used.

The arguments to PasteSpecial allows you to specifies options that you would see in the paste special option box:

The paste argument specifies the part of the range to be pasted. It may be one of the following Visual Basic constants: xlAll, xlFormulas, xlValues, xlFormats, or xlNotes. If it is not specified it is assumed to be xlAll.

The operations argument has one of the following values: xlNone, xlAdd, xlSubtract, xlMultiply, or xlDivide. If an operations argument is not specified it is assumed to be xlNone.

The skipBlanks argument has either the value True or False. If True, blank cells in the copied range will not be pasted into the destination range. If a skipBlanks argument is not specified then it is assumed to be False.

The transpose argument has either the value True or False. If True, rows and columns are transposed when the range is pasted. If a transpose argument is not specified, it is assumed to be False.

PASTESPECIAL AND ACTIVE SHEET BEHAVIOR

If the active sheet is the one where the PasteSpecial action is taking place, the selected range on the destination sheet changes to the range where the data is being pasted. The workaround is to store the selection, do the paste special, and then reset the selection. If the PasteSpecial is done to a non-active sheet, the selection on that sheet does not change!

Paste Method (Worksheet Object)

Pastes the contents of the Clipboard onto the sheet.

Syntax

expression.Paste(Destination, Link)

expression Required. An expression that returns a Worksheet object.

Destination Optional Variant. A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted, the current selection is used. This argument can be specified only if the contents of the Clipboard can be pasted into a range. If this argument is specified, the Link argument cannot be used.

Link Optional Variant. True to establish a link to the source of the pasted data. If this argument is specified, the Destination argument cannot be used. The default value is False.

Remarks

If you don’t specify the Destination argument, you must select the destination range before you use this method.

This method may modify the sheet selection, depending on the contents of the Clipboard.

Paste Method (Worksheet Object) Example

This example copies data from cells C1:C5 on Sheet1 to cells D1:D5 on Sheet1.

Worksheets("Sheet1").Range("C1:C5").Copy

ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")

What is the difference between the DataObject and the Clipboard?

The DataObject and the Clipboard both provide a means to move data from one place to another. As an application developer, there are several important points to remember when you use either a DataObject or the Clipboard:

· You can store more than one piece of data at a time on either a DataObject or the Clipboard as long as each piece of data has a different data format. If you store data with a format that is already in use, the new data is saved and the old data is discarded.

· The Clipboard supports picture formats and text formats. A DataObject currently supports only text formats.

· A DataObject exists only while your application is running; the Clipboard exists as long as the operating system is running. This means you can put data on the Clipboard and close an application without losing the data. The same is not true with the DataObject. If you close the application that put data on a DataObject, you lose the data.

· A DataObject is a standard OLE object, while the Clipboard is not. This means the Clipboard can support standard move operations (copy, cut, and paste) but not drag-and-drop operations. You must use the DataObject if you want your application to support drag-and-drop operations.

Tip You can define your own data format names when you use the SetText method to move data to the Clipboard or a DataObject. This can help distinguish between text that your application moves and text that the user moves.