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.
††††††††††† 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
††††††††††† '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
The following illustrates how to copy the same range on one sheet to another sheet:
††††††††††† Dim szRange As String
††††††††††† szRange = "C3:K3"
††††††††††† Worksheets("Data").Range(szRange).Copy _
††††††††††† ††††††††††† Destination:=Worksheets("Timesheet").Range(szRange)
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:
††††††††††† 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
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
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
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
'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:
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:
The following copies the range identified by an object variable
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.
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")
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:
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:
This next statement cuts range B4:D7 on the active sheet and pastes it to the right four columns.
This next† statement cuts row 6 and pastes it into row 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:
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:
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:
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.
ActiveSheet.Paste destination:=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:
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:
Sheets("Sheet5").Cells(5, 6) _
.PasteSpecial paste:=xlValues, _
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,
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.
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.
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.
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
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.