Udemy logo

excelVBA or Visual Basic for Application is an integration of Microsoft’s VisualBasic  with MS office applications such as MS Excel. VBA is run within the MS Office applications to build customized solutions and programs. These enhance the capabilities of those applications. Today we look at the important Paste Special feature of Excel and VBA. We assume that you have working knowledge of MS Excel and VBA. However, if you are not familiar with the concepts and commands of Microsoft Excel, we recommend that you go through our introductory VBA tutorial .

What is a Macro?

A  Macro is a compact piece of code which is created to customize MS office applications. Macros are created in VBA, which is a subset of Visual Basic and specifically designed to be user friendly. Instead of manually, coding repetitive tasks, it is more efficient to call Macros whenever required. This saves time, effort and money. To learn more about macros, you can take this course on VBA macros.

MS Excel Paste Special Commands

Paste Special is one of the features of Microsoft Office suite. MS Excel permits you to paste only specific aspects of cell data by using the Paste Special Feature. For example, if you need the results of a formula, but not the formula itself, you can choose to paste only the values calculated as the result of the formula. Pastes Special command is used to paste a wide variety of data aspects. Note that the Paste Special option is not applicable to cut data. In order for it to work a cell or range of cells must be copied.

How to use Paste Special in Excel 2010

When you copy a cell or range of cells, and paste it in the destination area you have two methods. They are Paste and Paste Special. Simple Paste does not change anything. However, Paste Special offers a number of options. Here we look at all the options offered in Paste Special Command.

Mathematical Operations

Paste Special also offers to do some simple mathematical calculations based on the values in source cells and the values in the destination cell range.

Other options of Paste Special:

To learn about other aspects of Excel 2010 VBA you can take this course.

Excel VBA PasteSpecialMethod

VBA PasteSpecial command pastes a range of cells from the clipboard in to the destination range of cells. The syntax of VBA PasteSpecial looks like this

expression .PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Where expression is a variable that represents a Range object.Let’s take a look at the various parameters of VBA PasteSpecial method. Note that the parameters are all optional.

Name

Required/Optional

Data Type

Description

Paste Optional XlPasteType The specific part of the cell range to be pasted.
Operation Optional XlPasteSpecialOperation Initiates the paste operation.
SkipBlanks Optional Variant Trueto  not paste blank cells in the range on the Clipboard into the destination range. The default value is False.
Transpose Optional Variant True to transpose rows and columns when the specified range is pasted.The default value is False.

 

Example 1

This is a simple example which demonstrates VBA PasteSpecial Method:

With Worksheets("Sheet1")
.Range("A1:A5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With

The values in cells D1:D5 on Sheet 1 is replaced with the sum of the existing content and the contents of cells A1:A5.

To see how this actually works, try out this VBA macro course by Mr Excel.

Example 2: Create a Macro to paste values into a new worksheet

We assume that you know how to open and work on VBA editor. We also assume that you are familiar with Macros in Excel VBA.  If not, we suggest that you read our tutorials on the same.  Here we look a program to create a macro to paste values into a new worksheet.

Sub ExamplePasteSpecial()
Dim ws As Worksheet, wb As Workbook
Set ws = ActiveSheet
Set wb = Workbooks.Add(xlWBATWorksheet)
ws.Range("C5:L19").Copy
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

In the program, we declare “ws” as variable of type Worksheet and “wb” as variable of type Workbook.  The source cell range is C5: L19. Note that the PasteSpecial function is invoked twice. In the first invocation, only the values calculated by the formula are pasted into the destination range. In the second invocation, only the formatting is copied and not the content.

Example 3: To Copy a Selected Cell and Paste it into another Cell

Sub PasteSpecial1()
Dim Sell_1As Range
Dim NewRowAs Long
Dim RngAs Range
Application.ScreenUpdating = False
NewRow = 401
Set Rng = Range("A1:A400")
For Each Sell_1InRng
Rows(Sell_1.Row & ":" &Sell_1.Row).Select
Selection.Copy
Rows(NewRow& ":" &NewRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
NewRow = NewRow + 1
Next Sell_1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

The PasteSpecial Function copies only the values to the destination cell range. No mathematical operations are performed, no blank cells are omitted and the transpose parameter is set to false. This is a classic example of using PasteSpecial Command in Excel VBA to create a Macro which automates the process.

Example 4:  Excel VBA Macro to Copy, Paste Special Values

Frequently we use formulas to extract data on to a specific worksheet. Then we want to remove the formulas and just keep the data. For that you would copy your selection , right click, chose paste special, select values, click ok and finally hit the enter key to achieve the desired results. Alternatively you could use the programming code below and assign it into a Macro to perform all the six steps mentioned above with a simple mouse click.

Sub CopyPasteSpecVal_1()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

In this program, the formulas are not copied. The destination cell range contains only the calculated values using the formulas. No blanks are skipped. The source cells are not transposed and no mathematical operations are done.

It does not make sense for programmers today to manually enter code and data. It is far better to have Macros to do this especially if the tasks are tedious and repetitive. The PasteSpecial function offers a number of useful options to paste data from the source range to the destination range. Use it wisely and benefit from its power. We hope this tutorial on Excel VBA Paste Special Method was informative. You can always learn more about Excel VBA with this awesome course from Infinite Skills.

Page Last Updated: March 2014

Excel VBA students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo