How to copy and paste range

Skip to first unread message

Mark Propst

Dec 31, 2001, 6:04:34 PM12/31/01
I know this is super basic but I've been mucking around for a while and
can't seem to get it right.
I'm just trying to copy a range from one sheet and paste it into each of a
group of subsequent sheets in the same.
It's been so long since I programmed in excel.vba that I've forgotten all
the simple stuff.
This is what I'm trying:

Dim SOURCE As Range
Dim DEST As Range
Dim CELL As Range
'Dim SELECTION As Collection
Set SOURCE = ActiveWorkbook.ActiveSheet.Range("A1,j10")
'fill a test range to try it out
For Each CELL In SOURCE.Cells
Cell.Value = "TEST"
'I thought that would fill all cells but it just fills the first and last!

'that line errors with"that command can't be used with multiple selections"
or something like that

'Set DEST = ActiveWorkbook.Sheets("TEST")
'I thought there would be a .Paste method but only found PasteSpecial in
object browser
'does pastespecial take args for what to paste?
'again "that command can't be used with multiple selections"

End Sub
I've been trying to look in the help files but it seems the vba help isn't
loaded on this machine.
does that take a special installation process? The IT guy is off today so
can't do anything about it rightnow.
Thanks for any ideas.

Dave Peterson

Dec 31, 2001, 7:45:55 PM12/31/01
Oh, I think you're going to smack yourself!

Try changing:

Set SOURCE = ActiveWorkbook.ActiveSheet.Range("A1,j10")


Set SOURCE = ActiveWorkbook.ActiveSheet.Range("A1:j10")

(this'll fix the multiple cell stuff, too.)


you can do things like:

source.copy destination:=worksheets("test").range("d9")

But take a look in Help for options for pastespecial. You'll see that they look
an awful lot like the same options that you get when you do it manually (and see
the dialog).

Excel 2002 shows the formats for both worksheet and range version of

In xl97, I think installing help was an option. In xl2k (and above), the option
even gets more optional. You could have it set up to install on first use.
Good luck with you IT guy.

Some of these types were added in xl2k or xl2002 (IIRC) and since you don't have
help installed:

PasteSpecial Method
See Also Applies To Example Specifics
PasteSpecial method as it applies to the Range object.

Pastes a Range from the Clipboard into the specified range.

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

expression Required. An expression that returns a Range object.

Paste Optional XlPasteType. The part of the range to be pasted.

XlPasteType can be one of these XlPasteType constants.
xlPasteAll default

Operation Optional XlPasteSpecialOperation. The paste operation.

XlPasteSpecialOperation can be one of these XlPasteSpecialOperation constants.
xlPasteSpecialOperationNone default

SkipBlanks Optional Variant. True to have blank cells in the range on the
Clipboard not be pasted into the destination range. The default value is False.

Transpose Optional Variant. True to transpose rows and columns when the range
is pasted.The default value is False.

PasteSpecial method as it applies to the Worksheet object.

Pastes the contents of the Clipboard onto the sheet, using a specified format.
Use this method to paste data from other applications or to paste data in a
specific format.

expression.PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex,
IconLabel, NoHTMLFormatting)

expression Required. An expression that returns a Worksheet object.

Format Optional Variant. A string that specifies the Clipboard format of the

Link Optional Variant. True to establish a link to the source of the pasted
data. If the source data isn’t suitable for linking or the source application
doesn't support linking, this parameter is ignored. The default value is False.

DisplayAsIcon Optional Variant. True to display the pasted as an icon. The
default value is False.

IconFileName Optional Variant. The name of the file that contains the icon to
use if DisplayAsIcon is True.

IconIndex Optional Variant. The index number of the icon within the icon file.

IconLabel Optional Variant. The text label of the icon.

NoHTMLFormatting Optional Variant. True to remove all formatting, hyperlinks,
and images from HTML. False to paste HTML as is. The default value is False.

Note NoHTMLFormatting will only matter when Format = “HTML”. In all other
cases, NoHTMLFormatting will be ignored.

You must select the destination range before you use this method.

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

As it applies to the Range object.

This example replaces the data in cells D1:D5 on Sheet1 with the sum of the
existing contents and cells C1:C5 on Sheet1.

With Worksheets("Sheet1")
.Range("D1:D5").PasteSpecial _
End With
As it applies to the Worksheet object.

This example pastes a Microsoft Word document object from the Clipboard to cell
D1 on Sheet1.

ActiveSheet.PasteSpecial format:= _
"Microsoft Word 8.0 Document Object"
This example pastes the same Microsoft Word document object and displays it as
an icon.

ActiveSheet.PasteSpecial _
Format:="Microsoft Word 8.0 Document Object", _


Dave Peterson


Jan 1, 2002, 12:42:14 PM1/1/02

Dave Peterson <> wrote in message

> Oh, I think you're going to smack yourself!

Duh! Maybe I'll do it twice for good measure!
Thanks very much for the info.


Jan 1, 2002, 4:39:07 PM1/1/02
"Mark Propst" <> wrote in message news:<#ZV7o7kkBHA.2388@tkmsftngp02>...

You use try:

Range("A1,J10").Value = "TEST"



Progressive Data Solutions
Home of VB Code Cutter - VB/VBA Code Library & Dev Tool with Free code

Reply all
Reply to author
0 new messages