How to copy and paste range
Mark Propst
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:
Sub PASTEMASTER()
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"
Next
'I thought that would fill all cells but it just fills the first and last!
'SOURCE.Cells.Copy
'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
'DEST.PasteSpecial
'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.
Mark
Dave Peterson
Try changing:
Set SOURCE = ActiveWorkbook.ActiveSheet.Range("A1,j10")
to
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
pastespecial.
===========
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
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats
Operation Optional XlPasteSpecialOperation. The paste operation.
XlPasteSpecialOperation can be one of these XlPasteSpecialOperation constants.
xlPasteSpecialOperationAdd
xlPasteSpecialOperationDivide
xlPasteSpecialOperationMultiply
xlPasteSpecialOperationNone default
xlPasteSpecialOperationSubtract
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
data.
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.
Remarks
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
Clipboard.
Example
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("C1:C5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
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.
Worksheets("Sheet1").Range("D1").Select
ActiveSheet.PasteSpecial format:= _
"Microsoft Word 8.0 Document Object"
This example pastes the same Microsoft Word document object and displays it as
an icon.
Worksheets("Sheet1").Range("F5").Select
ActiveSheet.PasteSpecial _
Format:="Microsoft Word 8.0 Document Object", _
DisplayAsIcon:=True
--
Dave Peterson
ec3...@msn.com
MP
Dave Peterson <ec3...@msn.com> wrote in message
news:3C3106C3...@msn.com...
> 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.
Mark
pds
You use try:
Range("A1,J10").Value = "TEST"
Regards
Graham
--------------
Progressive Data Solutions
http://www.pdsolutions.com.au
Home of VB Code Cutter - VB/VBA Code Library & Dev Tool with Free code
formatting/indenting