How does the PASTE.SPECIAL function work?
 

I'm working on a macro and I need to Paste values only for a range. I can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel 2002.

"Curt" wrote in message
...
I'm working on a macro and I need to Paste values only for a range. I
can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel
2002.

I don't know, and if you need to know quickly, get out your original install
disk and install the help files. IIRC, it was optional with Office 2000, and
that may also be true for Excel 2002.

"Curt" wrote in message
...
I'm working on a macro and I need to Paste values only for a range. I
can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel
2002.

OK...you got me curious. Here's what's in the XL 2000 help file. Good luck!

PasteSpecial Method (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.

Syntax

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

expression Required. An expression that returns a DialogSheet or 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.

Remarks

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.

PasteSpecial Method (Worksheet Object) Example

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

PasteSpecial Method (Range Object)


Pastes a Range from the Clipboard into the specified range.

Syntax

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

expression Required. An expression that returns a Range object.

Paste Optional Variant. The part of the range to be pasted. Can be one of
the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The
default value is xlPasteAll.

Operation Optional Variant. The paste operation. Can be one of the
following XlPasteSpecialOperation constants: xlPasteSpecialOperationNone,
xlPasteSpecialOperationAdd, xlPasteSpecialOperationSubtract,
xlPasteSpecialOperationMultiply, or xlPasteSpecialOperationDivide. The
default value is xlPasteSpecialOperationNone.

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 (Range Object) Example

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

I just recorded a macro and got this..........

Sub TestPasteSpecial()
Range("A1").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub


Vaya con Dios,
Chuck, CABGx3


"Curt" wrote in message
...
I'm working on a macro and I need to Paste values only for a range. I
can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel
2002.

If you are doing this via VBA you don't need to use the pastespecial, you can
simply use code like this:

Range("P9:P17").Value = Range("N9:N17").Value

or if you are pasting the values over the original calculations then

With Range("N9:N17")
.value = .value
End With

Hope this helps
Rowan

"Curt" wrote:

I'm working on a macro and I need to Paste values only for a range. I can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel 2002.

Thanks for the help. I'm actually using an old Macro and just trying to
modify it for my purposes. It wasn't written using vba, it's typed out in an
excel spreadsheet. But there's a function in excel itself called
Paste.Special(...) that I can't get help for. I'd need to use this function
for this type of macro or start from scratch. Can anyone tell me how that
function works? Thanks.

Curt

"Curt" wrote:

I'm working on a macro and I need to Paste values only for a range. I can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel 2002.

Curt -

PASTE.SPECIAL is an Excel 4 XLM (macro programming language) function. It is
not a VBA function.

You can get information for obtaining an XLM help file by going to
www.microsoft.com and searching for "macrofun.exe" (without the quotes).

- Mike
www.mikemiddleton.com

"Curt" wrote in message
...
I'm working on a macro and I need to Paste values only for a range. I
can't
get the help file for macros for some reason. I guess I just need to know
the propper syntax for that function if anyone knows. I'm using Excel
2002.