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