How to use Paste Special in Excel Using VBA?

Paste Special is a dialog box in Excel which is used for pasting the data in a different format. Paste Special has many options to use. We use Paste Special as paste formula, values, formats, comments validation, and many more options.

Paste Special option enable while we copy the data in Excel Sheet if we use to cut command in Excel then Paste Special option doesn’t enable.

We use paste special feature from VBA and Excel dialog box.

In this blog, we will use Paste Special feature from VBA code in Excel and given example below based on Paste Special options.

Paste All Data

paste special in excel

Sub Paste_All_Data()

    ‘paste all

    ‘Both give same result

    Sheet2.Range(“a1”).CurrentRegion.Copy Sheet1.Range(“a1”)

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose destination where you have to paste

    Sheet1.Range(“a15”).PasteSpecial (xlPasteAll)

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste as Values

paste special excel vba

Sub Paste_As_Value()

    ‘paste value

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“p29”).PasteSpecial xlPasteValues

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Formula and Number Format

paste special excel vba code

Sub Paste_Format_Formula()

    ‘paste only formula and format

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“p1”).PasteSpecial xlPasteFormulasAndNumberFormats

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Format

excel vba code paste special values

Sub Paste_As_Format()

    ‘paste only format

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“a43”).PasteSpecial xlPasteFormats

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Comment

excel vba code for paste special

Sub Paste_As_Comments()

    ‘paste only commnents

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“a1”).PasteSpecial xlPasteComments

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Validation

Sub Paste_As_Validation()

    ‘paste only validation

    Sheet2.Range(“j2”).Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“p15:p27”).PasteSpecial xlPasteValidation

    ‘when we clear copy mode then we apply this syntax

    Sheet1.Range(“p15:p27”).Interior.Color = vbGreen

    Application.CutCopyMode = False

End Sub

Paste Column Width

How to use Paste Special in Excel Using VBA?

Sub Paste_Columns_Width()

    ‘paste only columns widths

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“a29”).PasteSpecial xlPasteColumnWidths

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Except Borders

Sub Paste_Data_Without_Border()

    ‘paste data without borders

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“a15”).PasteSpecial (xlPasteAllExceptBorders)

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Formula only

Sub Paste_With_Formula()

    ‘paste data with formula

    Sheet2.Range(“a1”).CurrentRegion.Copy

    ‘choose detination where you have to paste

    Sheet1.Range(“a43”).PasteSpecial xlPasteFormulas

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

How to use Paste Special in Excel Using VBA?

Transpose Data Row to Column

Sub Transpose_Row()

    ‘transpose data rows to columns

    Sheet2.Range(“q1”).CurrentRegion.Copy

    ‘select range where you want paste

    Sheet1.Range(“y2”).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, skipblanks:=False, Transpose:=True

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Transpose Column to Row

Sub Transpose_Column()

    ‘transpose data colums to row

    Sheet1.Range(“y2:ak2”).CurrentRegion.Copy

    ‘select range where you want paste

    Sheet1.Range(“y5”).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, skipblanks:=False, Transpose:=True

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Add Value in Existing Data

Sub Add_Number()

    ‘Add number

    Sheet2.Range(“L2”).Copy

    ‘select range where you want paste

    Sheet1.Range(“B2:B13”).PasteSpecial (xlPasteAllExceptBorders), xlPasteSpecialOperationAdd

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Subtract Value in Existing Data

Sub Subtract_Number()

    ‘Subtract

    Sheet2.Range(“L2”).Copy

    ‘select range where you want paste

    Sheet1.Range(“c2:c13”).PasteSpecial (xlPasteAllExceptBorders), xlPasteSpecialOperationSubtract

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Multiply Value in Existing Data

Sub Multiply_Number()

    ‘Multiply

    Sheet2.Range(“m2”).Copy

    ‘select range where you want paste

    Sheet1.Range(“e2:e13”).PasteSpecial (xlPasteAllExceptBorders), xlPasteSpecialOperationMultiply

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Divide Value in Existing Data

Sub Divide_Number()

    ‘Divide

    Sheet2.Range(“m2”).Copy

    ‘select range where you want paste

    Sheet1.Range(“d2:d13”).PasteSpecial (xlPasteAllExceptBorders), xlPasteSpecialOperationDivide

    ‘when we clear copy mode then we apply this syntax

    Application.CutCopyMode = False

End Sub

Paste Link

Sub Paste_Link()

    ‘paste link

    Sheet1.Range(“z5:z17”).Copy

    Sheet1.Range(“ab5”).Select

    Sheet1.Paste link:=True

    Application.CutCopyMode = False

End Sub

Download Workbook 

How to use Paste Special in Excel Using VBA?

What is Procedure in VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *