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
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
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
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
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
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