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:

  1. Font color
  2. Font face
  3. Font style like bold/italic/underline
  4. Font size
  5. Size of cells
  6. Word wrap within cells
  7. Background color of cells
  8. Comments within cells
  9. Borders and shading
  10. Table formatting
  11. Formulas
  12. Direct data/values instead of formulas
  13. Conditional formatting based on cells values and range
  14. Cell styles that are predefined
  15. Percentage format
  16. Currency format
  17. Date and time format
  18. Text format
  19. Accounting format
  20. Merge and center
  21. Transpose

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.

Paste Special dropdown in Excel

We can see several options here.

Various options in the Paste Special function in Excel
I’ll choose only “Values” option and click on “OK” button.
Choosing the "Values" option in Paste Special

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:

Output of the Paste Special formula

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
Output of the cub-procedure
Results of the cub-procedure

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
Paste Special function in columns

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
Paste Special function in rows

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 into only one cell

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 with number formats

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

Paste Special with formulas

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”

Automated feature when doing a formula Paste Special

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
Copy and pasting the color, borders, and style formats of a cell

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.

Skipping copying blanks
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
Output of code skipping blanks

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 

Output:

Output of Paste Special - Transpose function

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.

Paste Special - comments function

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
Output of the Paste Special - Comments function

Output:

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.

Conclusion

As elaborated above, paste special feature can also be used to copy many other parameters of the cells in sheets like:

  • Data validation settings – xlPasteValidation

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

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 – xlPasteAllExceptBorders
  • Column widths – xlPasteColumnWidths

This copies and pastes only the column width value from the source cells to the destination cells.

  • All merging conditional formats – xlPasteAllMergingConditionalFormats

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.

One thought on “How to Use Paste Special in VBA (Examples)”

Leave a Reply

Your email address will not be published.