How to Use Paste Special in VBA (Examples)
Cut, copy, and paste are very popular commands that any user knows how to use. It is the same with Microsoft Excel as well. In fact, these commands are very useful in Excel when we want to replicate calculation steps or automate a calculation.
As we all know, there are a lot of features and formatting involved in Excel. Some of the many examples:
- Font color
- Font face
- Font style like bold/italic/underline
- Font size
- Size of cells
- Word wrap within cells
- Background color of cells
- Comments within cells
- Borders and shading
- Table formatting
- Direct data/values instead of formulas
- Conditional formatting based on cells values and range
- Cell styles that are predefined
- Percentage format
- Currency format
- Date and time format
- Text format
- Accounting format
- Merge and center
And so on.
Partial Pasting In Microsoft Excel
Because of the numerous features available in Excel cited above, there is a special paste feature which can help us copy and paste only any specific properties from one range of cells to another. This feature is called “Paste Special.”
After copying a specific cell, you can try to right click on the destination cell to see “Paste Special” as an option in the context menu.
In this example below, we have copied cell D6 and right clicked after selecting cell G6. The context menu has “Paste Special” option. Click on the same.
We can see several options here.
Now we can see that the value has alone been copied from the source cell to the destination cell. The colors, size, font face, font style, and other properties have not been pasted.
This is the advantage of using “Paste Special.”
As seen in the “Paste Special” dialog box in one of the images above, the properties in the radio buttons can be chosen one at a time to be copied from a range of cells to another.
Paste Special Using VBA Macros
The same “Paste Special” action can be coded using VBA and later used in automation as well. Let’s take a look at the code to paste each property one-by-one.
Paste Special – Values
Here is a simple piece of code that can do a paste special, copying only the values from cell B5 to G5.
Sub paste_spl_demo() ' copy from cell B5 Range("B5").Copy ' paste to cell G5 Range("G5").PasteSpecial Paste:=xlPasteValues End Sub
Here is the output of this code:
Copy And Paste Values From One Sheet To Another
This cub-procedure will copy a range of cells from “Source” sheet and paste it into another range of cells in a sheet named “Destination.”
Sub paste_spl_demo() ' copy from sheet "Source" Sheets("Source").Range("B2:B15").Copy ' paste to sheet "destination" Sheets("Destination").Range("B2:B15").PasteSpecial Paste:=xlPasteValues End Sub
Paste Special On Rows And Columns
This same copy & paste using “Paste Special” can be done on rows/columns too.
Copy – Paste Columns
Sub paste_spl_demo_col() ' copy from sheet "Wonders" - Col B Sheets("Wonders").Columns("B").Copy ' paste values to sheet "Wonders" - Col F Sheets("Wonders").Columns("F").PasteSpecial Paste:=xlPasteValues End Sub
Copy – Paste Rows
Sub paste_spl_demo_row() ' copy from sheet "Wonders" - Row 2 Sheets("Wonders").Rows(2).Copy ' paste values to sheet "Wonders" - Row 12 Sheets("Wonders").Rows(12).PasteSpecial Paste:=xlPasteValues End Sub
Copy One Range and Paste Only into One Cell
In this program, we will copy a range of cells covering a few columns and rows. But we will paste it into a single cell. The range automatically gets calculated, and the values get pasted.
Sub paste_spl_demo() ' copy from Range B5 to C14 Sheets("Source").Range("B5:C14").Copy ' paste to cell G5 Sheets("Source").Range("G5").PasteSpecial Paste:=xlPasteValues End Sub
Paste Special – Number Formats
When we use paste values, some number formats like, percentage/currency do not get pasted. For this reason, we can paste number formats along with the values. Here is a program which copies values in the percentage format from the source cell and uses the paste special feature to achieve the desired format.
Sub paste_spl_demo() ' copy from col C Range("C1:C5").Copy ' paste values and numberformat to col G Range("G1:G5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats End Sub
Paste Special – Formulas
Here is the code to paste formulas alone from a range of cells to another.
Sub paste_spl_demo() ' copy from col D Range("D:D").Copy ' paste formuals to col E Range("E:E").PasteSpecial Paste:=xlPasteFormulas End Sub
The formula was B2/C2 in Cell D2. When it got pasted to cell E2, it changed to C2/D2. This is an automated feature. If you do not want this automatic change, use a “$” before each letter that indicates the col in the formula. Eg: “=$B2/$C2”
Special Pasting of Formats Only
This feature will copy and paste only the color, borders, and style formats from the source range to the destination range using the paste special feature.
Sub paste_spl_demo() ' copy from col C Range("D:D").Copy ' paste formats to col G Range("G:G").PasteSpecial Paste:=xlPasteFormats End Sub
Paste Special – Skip Blanks
As data is pasted from one cell to another, we can skip copying blanks if we want. Copying of blank cells to the destination can be skipped if we set this parameter to “True.” In the example below, we would copy Col C to Col D (skipping blanks) and Col E (without skipping blanks–default). In the image below, please notice that there are default values and formatting in two cells per destination col where the data of blank cells from Col C are supposed to get pasted.
Sub paste_spl_demo() ' copy from col C Range("C:C").Copy ' paste skipping blanks to col D Range("D:D").PasteSpecial SkipBlanks:=True ' paste skipping blanks to col E Range("E:E").PasteSpecial SkipBlanks:=False End Sub
After running the code, the output we see clearly shows that if the Col C is pasted to Col D with
Skipblanks:=True, the blank values are skipped. If not, it gets copied as in Col E.
Paste Special – Transpose
A transpose is a display of row data as a column and vice versa. Here, in this example, we will copy data from Col C (9 cells vertically C 1 to C8) and paste it as a row in Row 11 starting from cell A11 until Cell H11.
Sub paste_spl_demo() ' copy from col C Range("C1:C8").Copy ' paste the transpose of the copied data to Row 11. Range("A11:H11").PasteSpecial Transpose:=True End Sub
In the output we can see that the row of data has been pasted as a column of data in the destination range of cells.
Paste Special – Comments
This feature can be used if only the comments need to be pasted from one range of cells to another.
In the above example, we will try to copy contents from Col C to Col D but only using Paste Special – Comments feature.
Sub paste_spl_demo() ' copy from col C Range("C:C").Copy ' paste skipping blanks to col D Range("D:D").PasteSpecial xlPasteComments End Sub
The same comment can be seen pasted in the same row of col D (the destination range).
We can note that only the comments have been copied and not the format/values/formulas of the source cells.
As elaborated above, paste special feature can also be used to copy many other parameters of the cells in sheets like:
- Data validation settings –
This is the feature in which data in a cell should be selected from a dropdown list of values defined in another sheet/a specific list of hardcoded values.
- Source theme settings –
This is all about the theme settings of a cell like bold, italic, font size, etc. that are predefined.
- All data and format except the borders of a cell –
- Column widths –
This copies and pastes only the column width value from the source cells to the destination cells.
- All merging conditional formats –
This is used when the copy and paste of values and formats need to happen while retaining the conditional formatting already present in the destination cells. Both the formats of source and destination cells are merged here.
Once the paste special feature is used through VBA, it cannot be reversed or undone. So, we should take care that the logic is valid and also make a backup of the document before running this code.