Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

VBA Paste Special to Copy Values and Formats in Excel (9 Examples)

When we need to copy and paste any data in Microsoft Excel, we have the facility of different shortcut and ribbon tools in Excel. Excel VBA Macro also have such facility rather than workbook options. We will discuss the VBA Paste Special values and formats in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


9 Examples to Copy Values and Formats with Paste Special in Excel VBA

In this article, we will discuss 9 methods with VBA Macros to Paste Special values and format in Excel.

We will consider the dataset below for this purpose.


1. Apply InputBox in VBA Paste Special to Copy Values and Formats

We will use the InputBox option in this example for Paste Special.

Step 1:

  • Go to the Developer tab.
  • Click on Record Macro.
  • Set Excel_Paste_Special_1 as the Macro name.
  • Then press OK.

Step 2:

  • Now, click the Macros command.
  • Select the Macro and then press Step Into.

Apply InputBox for Paste Special Values and Formats in Excel VBA

Step 3:

  • Copy and paste the following code on the command module.
Sub Excel_Paste_Special_1()
Dim Copy_Cell As Range, Paste_Cell As Range
xTitleId = "Salary_Sheet"
Set Copy_Cell = Application.Selection
Set Copy_Cell = Application.InputBox("Select Range to Copy :", xTitleId, Copy_Cell.Address, Type:=8)
Set Paste_Cell = Application.InputBox("Paste to any blank cell:", xTitleId, Type:=8)
Copy_Cell.Copy
Paste_Cell.Parent.Activate
Paste_Cell.PasteSpecial xlPasteValuesAndNumberFormats
Paste_Cell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Apply InputBox for Paste Special Values and Formats in Excel VBA

Step 4:

  • Press F5 to run the code.
  • A new dialog box will appear. Select the source range on that box.
  • Then press OK.

Apply InputBox for Paste Special Values and Formats in Excel VBA

Step 5:

  • Another dialog box will appear. Choose a blank range where to paste the copied cells.

Apply InputBox for Paste Special Values and Formats in Excel VBA

Now, look at the dataset.

All data are copied with values and formats using the VBA Paste Special.

Read More: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel


2. Insert Cell Range with Values and Formats Using xlPasteAllUsingSourceTheme in VBA Paste Special

We will insert the cell range directly on the VBA code in this section.

Step 1:

  • Press Alt+F11 to enter the command module.
  • Put the following code on the command module.
Sub Excel_Paste_Special_2()
Range("B4:C9").Copy
Range("E4").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Insert Cell Range with xlPasteAllUsingSourceTheme to Paste Special Values and Formats in VBA

Step 2:

  • Now, press F5 to run the code.

This xlPasteAllUsingSourceTheme command copies the exact duplicate of the source data.

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell


3. Use VBA Variable to Insert Range with Values and Formats Using Paste Special

We will show the use of variables in the VBA Paste Special code here.

Step 1:

  • First, enter the command module by pressing Alt+F11.
  • Wite the code below in the command module.
Sub Excel_Paste_Special_3()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Range("B4:C9")
Set paste_rng = Range("E4")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Use Variable to Insert Range and Paste Special Values and Formats

Step 2:

  • Press F5 and run the code.

Read More: Excel VBA: Copy Range to Another Workbook


4. Use xlPasteValues and xlPasteFormats to Copy Values and Formats in Other Sheet Keeping the Formats Unchanged

The above methods were used for copying and pasting data in the same sheet. Now, we will show how to use the VBA Paste Special for different sheets.

Step 1:

  • Enter the command module by clicking Alt+F11 and copying the following code on it.
Private Sub Excel_Paste_Special_4()
  Application.ScreenUpdating = False
  Dim source_rng As Worksheet
  Dim paste_rng As Worksheet
  Set source_rng = Worksheets("Data_Set")
  Set paste_rng = Worksheets("Different_Sheet")
  Set Destination = paste_rng.Range("B2")
  source_rng.Range("B2:C9").Copy
   Destination.PasteSpecial Paste:=xlPasteValues
   Destination.PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Use xlPasteValues and xlPasteFormats to Copy Values and Formats in Other Sheet Keeping the Formats Unchanged

Step 2:

  • Press F5 to run the code.

We can see that data of the Data_Set is copied to Different_Sheet.

Read More: How to Use VBA PasteSpecial for Formulas and Formats in Excel (3 Ways)


Similar Readings


5. Use xlPasteFormats to Paste the Formats Only

In the previous methods, we copied the whole data and pasted it with no change. But in this section, we will copy only the format of data.

Step 1:

  • Go to the command module by pressing Alt+F11.
  • Copy and paste the code below in the command module.
Sub Excel_Paste_Special_5()
Range("B2:C9").Copy
Range("E2").PasteSpecial xlPasteFormats
End Sub

Use xlPasteFormats to Paste the Formats Only

Step 2:

  • Now, press the F5 button and run the code.

Look at the dataset. Only the formats are copied, no values are present here.

Read More: How to Copy and Paste in Excel Without Changing the Format


6. Use xlPasteValues to Paste the Values Only

We can copy the values only using the VBA Paste Special.

Step 1:

  • Press Alt+F11 to enter the command module.
  • Write the following code on the command module.
Sub Excel_Paste_Special_6()
Range("B4:C9").Copy
Range("E4").PasteSpecial xlPasteValues
End Sub

Use xlPasteValues to Paste the Values Only

Step 2:

  • Click on F5 and run the code.

Notice the dataset. Only values are copied here. No formats are copied in this method.

Read More: How to Use VBA to Paste Values Only with No Formatting in Excel


7. Copy and Paste All Contents of a Single Cell

In the previous sections, we copied a range of data. Here, we will copy only a single cell.

Step 1:

  • Now, press Alt+F11 and enter the command module.
  • Type the following code on the module.
Sub Excel_Paste_Special_7()
Range("B4").Copy
Range("E4").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Copy and Paste Contents of a Single Cell in VBA

Step 2:

  • Now, press the F5 button to run the code.

We can see that a single cell is copied here, not a range.

Read More: Formula to Copy and Paste Values in Excel (5 Examples)


8. Copy and Paste All Attributes of a Certain Column with VBA

Like a single cell, we can copy a single column in the dataset.

Step 1:

  • Simply press the Alt+F11 to enter the command module.
  • Write the code below in the module.
Sub Excel_Paste_Special_8()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Columns("C")
Set paste_rng = Columns("E")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Copy and Paste Attributes of a Certain Column in VBA

Step 2:

  • Run the code by pressing F5.

Here, we can see that Column B is copied to Column E.

Read More: [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)


9. Copy and Paste All Attributes of a Row with Excel VBA

Similarly, we can copy a single row using the Paste Special.

Step 1:

  • Click on Alt+F11 to enter the comand module.
  • Copy the following code on the command module.
Sub Excel_Paste_Special_9()
Dim source_rng As Range, paste_rng As Range
Set source_rng = Rows("4")
Set paste_rng = Rows("11")
source_rng.Copy
paste_rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Copy and Paste Attributes of a Row in Excel VBA

Step 2:

  • Run the code by pressing F5.

Here, Row 4 is copied to Row 11.

Read More: How to Copy Multiple Rows in Excel Using Macro (4 Examples)


Conclusion

In this article, we’ve explained 9 methods of VBA Paste Special values and formats in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo