Paste Special Vba

Copy A Cell Range To The Clipboard. First, let's take a look at how you can copy all of the items. The most obvious way to open the Paste Special dialog is clicking Paste Paste Special on the Home tab, in the Clipboard group: 2. Paste Special command in the right-click menu Alternatively, you can right-click a cell where you want to paste the copied data, and then click Paste Special in the context menu. Example #3 – VBA Transpose of an array with Paste Special Method. We can also transpose the array and paste as special as well do in Excel using Alt + E + S. We can use different operations under this paste special method. Let us consider the same data as we have used in the previous example.

  1. Paste Special Vba Excel
  2. Paste Special Vba Syntax
  3. Paste Special Vba Code
  4. Excel Paste Special Vba
  5. Paste Special Vba Excel
-->

Set paste values as default paste when using Ctrl + V with VBA code When you paste the data as values only, you need to click the little square at the corner of your selection and choose Values option each time after pasting the data. The following VBA code can help you to change the default paste to paste values.

Pastes the contents of the Clipboard, using a special format.

Syntax

Paste Special Vba Excel

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

expression A variable that represents a Shapes object.

Parameters

NameRequired/OptionalData typeDescription
DataTypeOptionalPpPasteDataTypeA format for the Clipboard contents when they're inserted into the document. The default value varies, depending on the contents in the Clipboard. An error occurs if the specified data type in the DataType argument is not supported by the clipboard contents.
DisplayAsIconOptionalMsoTriStateMsoTrue to display the embedded object (or link) as an icon.
IconFileNameOptionalStringIf DisplayAsIcon is set to msoTrue, this argument is the path and file name for the file in which the icon to be displayed is stored. If DisplayAsIcon is set to msoFalse, this argument is ignored.
IconIndexOptionalLongIf DisplayAsIcon is set to msoTrue, this argument is a number that corresponds to the icon you want to use in the program file specified by IconFilename. For example, 0 (zero) corresponds to the first icon, and 1 corresponds to the second icon. If this argument is omitted, the first (default) icon is used. If DisplayAsIcon is set to msoFalse, then this argument is ignored. If IconIndex is outside the valid range, then the default icon (index 0) is used.
IconLabelOptionalStringIf DisplayAsIcon is set to msoTrue, this argument is the text that appears below the icon. If this label is missing, Microsoft PowerPoint generates an icon label based on the Clipboard contents. If DisplayAsIcon is set to msoFalse, then this argument is ignored.
LinkOptionalMsoTriStateDetermines whether to create a link to the source file of the Clipboard contents. An error occurs if the Clipboard contents do not support a link.

Return value

ShapeRange

Remarks

Adds the shape to the collection of shapes in the specified format. If the specified data type is a text data type, a new text box is created with the text. If the paste succeeds, the PasteSpecial method returns a ShapeRange object representing the shape range that was pasted.

The DataType parameter value can be one of these PpPasteDataType constants:

  • ppPasteBitmap
  • ppPasteDefault
  • ppPasteEnhancedMetafile
  • ppPasteHTML
  • ppPasteGIF
  • ppPasteJPG
  • ppPasteMetafilePicture
  • ppPastePNG
  • ppPasteShape
SpecialPaste special vba paste values

The DisplayAsIcon parameter value can be one of these MsoTriState constants.

ConstantDescription
msoFalseThe default. Does not display the embedded object (or link) as an icon.
msoTrueDisplays the embedded object (or link) as an icon.

An error occurs if there is no data on the Clipboard when the PasteSpecial method is called.

See also

Support and feedback

Paste Special Vba

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Paste Special Vba Syntax

-->

Pastes a Range object that has been copied into the specified range.

Syntax

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

expression A variable that represents a Range object.

Paste Special Vba Code

Parameters

NameRequired/OptionalData typeDescription
PasteOptionalXlPasteTypeThe part of the range to be pasted, such as xlPasteAll or xlPasteValues.
OperationOptionalXlPasteSpecialOperationThe paste operation, such as xlPasteSpecialOperationAdd.
SkipBlanksOptionalVariantTrue to have blank cells in the range on the clipboard not be pasted into the destination range. The default value is False.
TransposeOptionalVariantTrue to transpose rows and columns when the range is pasted. The default value is False.

Return value

Variant

Excel Paste Special Vba

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.

Support and feedback

Paste Special Vba Excel

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.