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


Related pages


sql mcq questions with answers pdfexcel convert to xmlweekly calendar template 2014 excelunshare excelexcel protection passwordadvanced excel vba programmingmeeting schedule template excelado microsofthow to create excel worksheethow to put a checkbox in excelvba filedialog selecteditemsbelajar macro excelexcel vba range arrayprotect columns in excel 2007data analyst interview questions for freshersvba with accessexcel timeline chart templatevba recordsetexcel autofit column widthscripting.filesystemobject opentextfilevba code examples for excelvbscript file existunprotect macro excelexcel graph titleformula to calculate difference between two datesexcel fixed column widthuserforms excelvba checkbox checkedddl dml dcl tcl commands in sql with exampleshyperlinks in excelexcel automatic row heightscrum meeting templatemacros in databasehow to add developer tab to the ribbonexcel vba shortcutexcel consolidate sheets into onerecordset vba exceldata analytics interview questions and answerswhile loops in vbaexcel template for project trackingchart title excel 2007how to insert a textbox in exceldelete duplicate excelfind duplicates in excel spreadsheetexcel vba cell formulafso objectexcel vba modulesvba application inputboxformula to merge cells in excelgantt project planner excelvlookup function excel 2010excel vba forms tutorialwhat do you mean by vlookup in excelexcel vba dashboard examplemacro formula in excelduplicate column excelexcel insert rowexcel dashboard templatesexcel vba worksheet changevbscript examples in notepadshortcut keys of excel 2007excel trimexcel team schedule templateuse of trim functionhow to use sumif formula in excelexcel vlookup case sensitiveexcel delete rowsfinding duplicates in excel 2010how to unhide in excel 2013range of cells in excel formulaexcel checkbox in cellms access docmd findrecord