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

People use VBA to run a macro for various purposes. For instance, we can use VBA to Paste values only with no formatting in Excel. In this article, I will show you 6 ways to use VBA to Paste values only with no formatting in Excel.

This is the dataset I am going to use to show VBA to Paste values only with no formatting. We have the ID and Name of some salesperson along with their Sales amount.

How to Use VBA to Paste Values Only with No Formatting in Excel: 6 Methods

1. Using VBA to Paste Values of a Range with No Formatting

In this section, I will illustrate how to use VBA in Excel to Paste a range with no formatting.

STEPS:

⇒ Go to Developer tab >> select Visual Basic.

⇒ Then go to Insert tab >> select Module

⇒ Module window will appear. After that, write down the following code.

Sub copy_a_range()
Range("B3:D5").Copy
Range("B12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Here I have created a sub procedure copy_a_range. I have used Copy and Range.PasteSpecial method and selected cell B3:D5 to copy and want to Paste it in cell B12. Also mentioned xlPasteValues in Paste as I just want the values with no formatting. I have set the Application.CutCopyMode False to avoid cut or copy mode.

⇒ Now Run the program by clicking the icon shown below.

⇒ You will see that Excel has copied the B3:D5 range and Pasted them in B12.

Read More: VBA Paste Special to Copy Values and Formats in Excel

2. Paste the Values of an Entire Row with No Formatting Using VBA

Here, I will show you how to Paste an Entire Row without any formatting using VBA.

STEPS:

⇒ Insert a module following method 1. Then write down the following code.

Sub copy_a_row()
ActiveSheet.Range("B10").EntireRow.Copy
ActiveSheet.Range("B12").EntireRow.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Here, I have created a Sub procedure copy_a_row and used the EntireRow property in the ActiveSheet. I have selected cells B10 and B12 which represent the entire 10th and 12th rows. Next, I have used Copy and Range.PasteSpecial method then set it to xlPasteValues as I just want the values with no formatting. Then, I have set the Application.CutCopyMode to False to avoid cut or copy selection mode.

⇒ Now if you Run the program following method-1, Excel will Paste the 10th row in the 12th row.

Read More: How to Copy Multiple Rows in Excel Using Macro

3. Applying VBA to Paste the Values of an Entire Column with No Formatting

In this section, you will see how to use VBA to Paste an Entire Column without any formatting in Excel.

STEPS:

⇒ Insert a module following method 1. Then write down the following code.

Sub copy_a_column()
ActiveSheet.Range("B10").EntireColumn.Copy
ActiveSheet.Range("F12").EntireColumn.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

To begin with, I have created a Sub procedure copy_a_row and used the EntireColumn property in the ActiveSheet. I have selected cells B10 and F12 which represent the entire B and F columns.
Next, I have used Copy and Range.PasteSpecial method then set it to xlPasteValues as I just want the values with no formatting.
Then, I have set the Application.CutCopyMode to False to avoid cut or copy mode.

⇒ Now if you Run the program following method-1, Excel will Paste the B column in the F column.

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

4. Paste Only Values of a Range with No Formatting from a Different Worksheet Using VBA in Excel

You can also Paste only values of a range with no formatting from a different worksheet using VBA in Excel.

STEPS:

⇒ Insert a module following method 1. Then write down the following code

Sub different_datasheet()
Sheets("Dataset").Range("B1:D10").Copy
Sheets("Copy from different worksheet").Range("B1:D10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Here I have declared a Sub procedure different_datasheet. Then I have copied B1:D10 from the “Dataset” sheet and Pasted them in B1:D10 of another datasheet named “Copy from different worksheet”. I have used Range.PasteSpecial method and also mentioned xlPasteValues in Paste as I just want the values with no formatting. I have set the Application.CutCopyMode False to avoid cut or copy mode.

⇒ Now Run the program following method-1. Excel will Paste the selected range to “Copy from different worksheet”.

5. Using VBA to Paste a Particular Range with Only Values and No Formatting

Now, I will show you another macro code to Paste a particular range with only values in Excel.

STEPS:

⇒ Insert a module following method 1. Then write down the following code

Sub Paste_a_particular_range()
Dim range_to_copy As Range, range_for_pasting As Range
Set range_to_copy = Range("B3:C5")
Set range_for_pasting = Range("F3")
range_to_copy.Copy
range_for_pasting.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Here I have declared a Sub procedure Paste_a_Particular_range. In this Sub procedure, I have declared two variables using Dim Statement. They are range_to_copy and range_for_pasting. Both are Range Object. Then I have set the range_to_copy as B3:C5 and range_for_pasting as F3. After that, I have used Range.PasteSpecial method and also mentioned xlPasteValues in Paste as I just want the values with no formatting. I have set the Application.CutCopyMode False to avoid cut or copy mode.

⇒ Then Run the program following method-1. Excel will Paste the B3:C5 in F3.

Read More: How to Use VBA PasteSpecial for Formulas and Formats in Excel

6. Paste Only Value of a Range in Exactly the Same Location of Another Worksheet Using VBA in Excel

In this method, you will learn how to Paste only the value of a Range in exactly the same location of another worksheet.

STEPS:

⇒ Insert a module following method 1. Then write down the following code

Sub copy_exact_location()
Dim rng As Range
Set rng = Worksheets("Dataset").Range("B1:D10")
Worksheets("copy exact location").Range("B1").Resize(rng.Rows.Count, rng.Columns.Count). _
Cells.Value = rng.Cells.Value
Application.CutCopyMode = False
End Sub

Here I have declared a Sub procedure copy_exact_location. In this Sub procedure, I have declared rng variables as Range using Dim Statement.
Then, I used the Set statement to assign the Worksheet name and the selected range to the declared variable rng.
Next, I used the Resize method to Paste the copied values without any formatting. Later, I have set up the Application.CutCopyMode to False to avoid cut or copy mode.

⇒ Then Run the program following method-1. Excel will Paste the range B1:D10 to exactly the same location of the “copy exact location” worksheet.

Read More: Excel VBA to Copy Only Values to Destination 

Practice Workbook

You should practice well to get the hang of anything. That’s why I have attached a practice sheet for you so that you can internalize how to use VBA in Excel to Paste values only with no formatting.

Download Practice Workbook

Conclusion

In this article, I have explained 6 ways to use VBA to Paste values only with no formatting. I hope you will find this helpful. Lastly, if you have any queries or suggestions, please leave them in the comment box.

Related Articles

• How to Paste From Clipboard to Excel Using VBA
• How to Copy Visible Cells Only without Header Using VBA
• Excel VBA: Copy Cell Value and Paste to Another Cell
• Excel VBA: Copy Row If Cell Value Matches
• Copy and Paste Values to Next Empty Row with Excel VBA
• How to Autofilter and Copy Visible Rows with Excel VBA