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.

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 

Output:

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

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.