VBA PasteSpecial Method of Range Object Explained with Examples

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

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 Name Enum Value Description
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 Name Enum Value Description
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.
LIMITED TIME OFFER

5 Comments

  1. Mahesh Rajmane October 15, 2015 at 12:04 PM - Reply

    Very nice very helpful…..keep it up

  2. Borge February 16, 2016 at 10:54 PM - Reply

    Totall amazing stuff here …. Do you also know how to via VBA get to the special paste from clipboard: “Paste Special as Unicode Text”…. We want to fill a range in Excel with rich text (HTML text) from an SQL ntext column. We are currently using the following snippet:
    With objProgramListDataSheet
    .Activate
    .Range(objProgramListDataSheet.Cells(1, 1), _
    objProgramListDataSheet.Cells(1, rstPrgData.Fields.Count)).Font.Bold = True
    .Cells(2, 1).CopyFromRecordset rstPrgData
    End With
    where the rstPrgData holds the html text – However, all the tags appear on the Excel range …. how to copy in as rich text / html text???

    (If we take the text of one cell and do a control-c and paste special as above (control+command+v on mac keyboard), the rich text appears and the tags disappear…..)

    Do you know how to do this on a range or as part of applying the .CopyFromRecordset method ???

  3. m1 March 18, 2016 at 5:24 PM - Reply

    Helpful in Creating Excel for E commerce Project

  4. Junior April 8, 2016 at 1:52 AM - Reply

    I Need help on this like ASAP please.
    How can I midify this code to copy values given that B4 is a formula. The code does exactly what I want so I want to use it, I just need it to be modified to convert the formula into values. Thanks

    Sheets(“Temp”).Select
    Range(“B4”).Copy Sheets(“Upload”).Range(“D” & Rows.Count).End(xlUp).Offset(1, 0)

  5. Geoff Cosgrove July 19, 2016 at 6:25 PM - Reply

    I have a For statement “For Runs = 1 to iter” (where iter is 1 to 1000 (variable)

    I want to show on the screen tab (Input) at cell AA160 the percentage of run i.e. “Percentage complete =runs/iter %”

    What Visual Basis code lines do I need to insert into my macro?

Leave A Comment