MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Newbie question

  Asked By: Colleen    Date: Aug 30    Category: MS Office    Views: 1824

The easiest way to learn this functionality is through the macro

Do one or two paste special commands while recording, and then analyse
the code.

The code should look something like this:

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

You can turn various switches from False to True.
The Object Browser in the VBA Editor is also of great help (Press F2
whilst in the Editor). Type in PasteSpecial as a search function, and
select the Range object.

Looking at the format of the function, the object model states that
Function PasteSpecial([Paste As XlPasteType = xlPasteAll], [Operation As
XlPasteSpecialOperation = xlPasteSpecialOperationNone], [SkipBlanks],
Member of Excel.Range

For a full list of XlPasteType, press F1 for Help. You should get the

PasteSpecial Method
See AlsoApplies ToExampleSpecificsPasteSpecial 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
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 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.

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.

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
Have a play around. You can customise it to your heart's desire.



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Newbie question Or get search suggestion and latest updates.