The PasteSpecial VBA Command: Get VBA to Do the Hard Work for You

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

• All–This is similar to the conventional paste.
• Formulas- pastes all the text, numbers and formulas in the selected cell without their formatting.
• Values– This option converts formulas from the copied cell to their calculated values in the destination cell.
• Formats– Here the content is not copied, only the formatting is copied from the source cells to the destination cells.
• Comments– This option pastes only the notes from the source cells to the destination cells.
• Validation– The option pastes only the Data Validation commands into the destination cell range.
• All Using Source Theme- When you select this option the cell styles are also copied along with the other content.
• All Except Borders– The entire content and the formatting without any borders is pasted into the destination cell range.
• Column Widths– Here the column widths of the sources cells are applied to the destination cells.
• Formulas and Number Formats- Includes the number formats of the source cells to the destination cells.
• Values and Number Formats – Here two things happen. Firstly, formulas are converted to their calculated values in the destination cells. Also the number formats of the source are applied to the destination cells.
• All Merging– When you select this option, the conditional formatting of the source cells are applied to the destination cell range.

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.

• None– This is the default setting. In this no operation is performed.
• Add– The values of the source cells are added to the value(s) in the destination cell.
• Subtract– This option subtracts the values copied from the source cell, from the destination cells values.
• Multiply-This option multiplies the values copied with the values of the destination cells. The result is stored in the destination cell(s).
• Divide- This is similar to multiply option except that division is done instead of multiplication.

Other options of Paste Special:

• Skip Blanks– If this option is selected only the non-empty cells are pasted.
• Transpose– Select this option if you want to change the orientation of the pasted entries.
• Paste Link– Select this option to establish a link between the source and destination cells. Here when the values of the original cells are updated or changed, the values of the destination cells are also correspondingly updated.

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.