{"id":9411,"date":"2023-05-18T13:23:26","date_gmt":"2023-05-18T13:23:26","guid":{"rendered":"https:\/\/www.excel-learn.com\/?p=9411"},"modified":"2024-02-10T10:18:51","modified_gmt":"2024-02-10T10:18:51","slug":"vba-pastespecial-method","status":"publish","type":"post","link":"https:\/\/www.excel-learn.com\/vba-pastespecial-method\/","title":{"rendered":"Excel \/ VBA PasteSpecial Method: Explained with 3 Examples"},"content":{"rendered":"

\"VBA<\/p>\n

\n

PasteSpecial function in VBA<\/strong><\/h3>\n

Normally, as you copy\/paste a cell or range<\/a> of cells in Excel, it pastes value, formatting (font color, interior color, italic, bold, etc.), Excel formulas, comments, etc. in the destination cells.<\/p>\n

In certain scenarios, you may need to only paste values to the destination cell rather than font color, backgrounds, or formulas.<\/p>\n

This is where the PasteSpecial option works in Excel as well as VBA macros.<\/p>\n<\/div>\n

Syntax:<\/strong><\/h3>\n
expression.PasteSpecial (Paste, Operation, SkipBlanks, Transpose)<\/div>\n

We will show you how to use the PasteSpecical function in VBA code with various arguments and paste options.<\/p>\n

An example of pasting only values<\/strong><\/h3>\n

Our sample sheet for this example contains cells with interior color. Whereas, the text color of a few cells is red and blue.<\/p>\n

By using PasteSpecial<\/strong> function, we will only copy\/paste values to the destination cells in the same sheet.<\/p>\n

VBA code:<\/strong><\/p>\n

Sub pasteSpe_ex()\r\n\r\nDim rng_paste As Range\r\n\r\nDim x\r\n\r\nSet rng_paste = Range(\"B5:D9\")\r\n\r\nrng_paste.Copy\r\n\r\n\r\n'Pasting only values to the destination range\r\n\r\nRange(\"G10:I15\").PasteSpecial Paste:=xlPasteValues\r\n\r\nEnd Sub<\/pre>\n
Result:<\/strong><\/p>\n
\"vba-pasteSpecial\"<\/p>\n
You can see in the above code that after using the range method in the source range, we used PasteSpecial function with the Paste argument.<\/p>\n
Paste:=xlPasteValues<\/div>\n
It enabled us to copy only values while font and interior colors were not pasted.<\/p>\n
Copy only formatting example<\/strong><\/h3>\n
This time we will only copy formatting to the destination range. Values are not copied. Have a look at the same sheet we used above:<\/p>\n
Sub pasteSpe_ex()\r\n\r\nDim rng_paste As Range\r\n\r\nSet rng_paste = Range(\"B5:D9\")\r\n\r\n\r\nrng_paste.Copy\r\n\r\n\r\n'use xlPasteFormats for copying formats only\r\n\r\nRange(\"G10:I15\").PasteSpecial Paste:=xlPasteFormats\r\n\r\nEnd Sub<\/pre>\n
Output:<\/strong><\/p>\n
\"vba-pasteSpecial-form\"<\/p>\n
You can see, the background\/interior colors of the destination cells are the same as the source.<\/p>\n
For showing the font color, we wrote some text in the pasted cells and you see it\u2019s also copied.<\/p>\n
Pasting formulas only<\/strong><\/h3>\n
\n