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
Next
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)
or
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.