REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

VBA PasteSpecial Method of Range Object is used to pastes a range from clipboard to the specified range in the worksheet.

VBA PasteSpecial Method Excel Range Object

VBA PasteSpecial method of Range – Syntax

Here is the syntax to VBA PasteSpecial method of range object .You can use the ‘Range.PasteSpecial’ method to do PasteSpecial for the specified range.

Range(“YourRange”).PasteSpecial(

[Paste As XlPasteType = xlPasteAll], [Operation As XlPasteSpecialOperation = xlPasteSpecialOperationNone], [SkipBlanks], [Transpose])

Here,
Paste:It is Optional argument. Paste specifies the part of the range to be pasted. Please find the ‘xlPasteType’ table at the bottom of the page for more information.
Operation:It is Optional argument. Operation specifies the how numeric data will be changed at the destination cells in the worksheet. Please find the ‘XlPasteSpecialOperation’ table at the bottom of the page for more information.
SkipBlanks:It is Optional argument. SkipBlanks contains boolean value either True or False. The default value is False. If SkipBlanks is True, It won’t copy the blank cells from the clipboard(Which we copied to dp pastespecial) to the destination range.
Transpose:It is Optional argument. Transpose contains boolean value either True or False. The default value is False. If Transpose is True, It will transpose the rows and columns when the range pasted.

VBA PasteSpecial method of Range – Example1

Here is the example for PasteSpecial method of range object to pastes a range from the clipboard to the specified range in the worksheet. Here you can use ‘Range.PasteSpecial’ method of range object.

Sub Range_PasteSpecial_Values()

    Range("C6:D11").Copy
    Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub

In the above example, we are copying the range(“C6:D11”) to the clip board. And the pasting the only values to the range G6 from the copied range.

Excel Paste Special Enumerations

Here are the available enumerations while copying and pasting the data using PasteSpecial method of Excel Range. Excel Paste Type (XlPasteType) and Excel Paste Operations (XlPasteSpecialOperation) helps to copy the data from source range to destination range in different situation with verity of criteria (based on our requirement).

Excel Paste Special Types- Enumerations

Here are the available enumerations for Excel Paste Type (XlPasteType): This will tell what content from the source range (copied range) to be pasted in the target or destination range.

Enum NameEnum ValueDescription

xlPasteValues -4163 When XlPasteType = xlPasteValues, Only values in the copied range are pasted.
xlPasteComments -4144 When XlPasteType = xlPasteComments, Only Comments in the copied range are pasted.
xlPasteFormulas -4123 When XlPasteType = xlPasteFormulas, Only Formulas in the copied range are pasted.
xlPasteFormats -4122 When XlPasteType = xlPasteFormats, Only format of the copied Range is pasted.
xlPasteAll -4104 When XlPasteType = xlPasteAll, default and everything from the copied range will be pasted.
xlPasteValidation 6 When XlPasteType = xlPasteValidation, Only Validations of the copied range are pasted.
xlPasteAllExceptBorders 7 When XlPasteType = xlPasteAllExceptBorders, Everything from the copied range will be pasted, except the borders.
xlPasteColumnWidths 8 When XlPasteType = xlPasteColumnWidths, Only column width of the copied range is pasted.
xlPasteFormulasAndNumberFormats 11 When XlPasteType = xlPasteFormulasAndNumberFormats, Only Formulas and Number formats are pasted.
xlPasteValuesAndNumberFormats 12 When XlPasteType = xlPasteValuesAndNumberFormats, Only Values and Number formats are pasted.
xlPasteAllUsingSourceTheme 13 When XlPasteType = xlPasteAllUsingSourceTheme, Everything will be pasted using the copied range theme.
xlPasteAllMergingConditionalFormats 14 When XlPasteType = xlPasteAllMergingConditionalFormats, Everything will be pasted and conditional formats of the source and destination ranges will be merged .

Excel Paste Special Operations – Enumerations

Here are the available enumerations for Excel Paste Special Operations (XlPasteSpecialOperation ): This will be different arithmetic operations on the destination range:

Enum NameEnum ValueDescription

xlPasteSpecialOperationAdd 2 When XlPasteSpecialOperation = xlPasteSpecialOperationAdd, addition operation will be performed on the destination range. i.e; copied value will be added to the values in the destination range.
xlPasteSpecialOperationSubtract 3 When XlPasteSpecialOperation = xlPasteSpecialOperationSubtract, substraction operation will be performed on the destination range. i.e; copied value will be subtracted from the values in the destination range.
xlPasteSpecialOperationMultiply 4 When XlPasteSpecialOperation = xlPasteSpecialOperationMultiply, mltiplicaation operation will be performed on the destination range. i.e; copied value will be multiplied to the values in the destination range.
xlPasteSpecialOperationDivide 5 When XlPasteSpecialOperation = xlPasteSpecialOperationDivide, Divide operation will be performed on the destination range. i.e; copied value will be divided the values in the range cell.
xlPasteSpecialOperationNone -4142 When XlPasteSpecialOperation = xlPasteSpecialOperationNone, default and no calculations will be pefromed when pasted.
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates