PasteSpecial VBA – Other Examples

In the previous blog, we learned to use the basic functionality of the PasteSpecial method in VBA. Now we will learn to implement the other examples of the PasteSpecial method in VBA. So let us start learning.

Application.CutCopyMode

When we use the copy method to copy a range of cells in VBA, it automatically enables the cut copy mode. After we paste the range to the destination, the cut copy mode is not exited automatically and the user needs to press the escape key to exit from the cut copy mode.

exit from cut copy mode using VBA

We can use the Application.CutCopyMode property to exit from this mode with the help of VBA. Use the following line of code.

Application.CutCopyMode=False
infographics paste special using VBA

Examples to Implement Paste Special Using VBA

In this section of the article, we will learn to implement practical examples to use the PasteSpecial method using VBA.

Example 1 – Paste Column Width Using VBA

We can only paste the column width of the source range using PasteSpecial in VBA. This feature is used more often, It copies the width of columns in the copied range and then pastes the column width change to the destination range.

For instance, you can use the following Sub Procedure to copy the column width of the range A1:D4 to F1:H4.

Sub pasteColWidth()

'paste column width using VBA
Range("A1:D4").Copy
'**************************************************

Range("F1").PasteSpecial Paste:=xlPasteColumnWidths

'exit from cut copy mode
Application.CutCopyMode = False
'**************************************************

End Sub
paste special to paste column width using VBA

So you see that the column width from columns A to D is copied to the columns F to I in the destination range.

Example 2 – Paste Formulas and Number Formatting

Let us suppose we have students’ marks in three subjects as follows.

paste number format and formulas using VBA

We are using the following formula in the range E1:E6 to get the percentage of each student.

=SUM(B2:D2)/300

Thereafter we change the number formatting of the percentage column to Percentage.

change the number formatting to percentage

Now we can use the following Sub procedure to copy the formula and number formatting in the range A1:E6 with the help of paste special. The copied range can be referred to as the current region around any cell of the range.

Sub pasteFormulasAndNumberFormatting()

'copy the formulas and number formatting
Range("A1").CurrentRegion.Copy
'***************************************************************

'use paste speical to paste
Range("A8").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
'***************************************************************

'exit from cut copy mode
Application.CutCopyMode = False
'***************************************************************

End Sub
paste special to paste formulas and number formatting

Example 3 – Paste Special Transpose

We can use the paste special in VBA to paste the transpose of the range. Set the Transpose argument of the PasteSpecial method to True.

Sub pasteTranspose()
'paste special transpose

'copy the range around cell A1
Range("A1").CurrentRegion.Copy
'***************************************

'paste transpose to cell B1
Range("B1").PasteSpecial Transpose:=True
'***************************************

End Sub
paste special to paste transpose using VBA

Example 4 – Paste Special Skip Blanks

We can set the Skip Blanks argument of the PasteSpecial method to True. This will only paste the Nonempty cells to the destination range. For instance, look at Range A, Range B, and Range C below.

skip blanks paste special using VBA

We will use the paste special to not skip blanks in range B and skip blanks in range C.

Sub pasteSkipBlanks()

'paste special to skip blanks

'copy the range A2:A8
Range("A2:A8").Copy
'*****************************************

'paste without skipping blanks to range B
Range("C2").PasteSpecial SkipBlanks:=False
'*****************************************

'paste with skipping blanks to range C
Range("E2").PasteSpecial SkipBlanks:=True
'*****************************************

End Sub
paste special to skip blanks using VBA

Example 5 – Paste Special Comments

We can use the Paste Special to copy the comments to another range. You can use the PasteSpecial method and set the Paste argument to xlPasteComments.

Sub pasteComments()

'paste comments in range

'copy the range
Range("A1").Copy
'**********************************************

'paste the comment to cell A6
Range("A6").PasteSpecial Paste:=xlPasteComments
'**********************************************

End Sub
paste comments using paste special in VBA

Example 6 – Paste Special To Paste Data Validation

Here we have created a Drop Down List for Range A1:A4 using a list with three possible values as apple, mango, and banana.

copy data validation using VBA in excel

We wish to copy the Data Validation on Range A1:A4 to C1:C4. Use the following macro.

Sub pasteDataValidation()

'paste data validation in range

'copy the range
Range("A1:A4").Copy
'**********************************************

'paste the data validation to cell A6
Range("C1").PasteSpecial Paste:=xlPasteValidation
'**********************************************

End Sub
paste data validation in excel using VBA

This brings us to an end.

Thank you for reading.❤

Leave a Comment