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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel vba paste values only no formatting


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.

excel vba paste values only no formatting

 Then go to Insert tab >> select Module

excel vba paste values only no formatting

 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

excel vba paste values only no formatting

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.

excel vba paste values only no formatting

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

excel vba paste values only no formatting

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.

excel vba paste values only no formatting

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

excel vba paste values only no formatting

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

excel vba paste values only no formatting

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”.

excel vba paste values only no formatting


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

excel vba paste values only no formatting

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 locationworksheet.

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

3 Comments
  1. Howdy! I have been trying to create a filepath using a cell as a variable, and then using that filepath (with sheet and cell) to get a value without using Indirect, because I don’t want to have to open and close a hundred files.

    What I have done is used all the network address parts in cells with the file name as the variable, then concatenated that into the four sheet/cell combinations I needed. I then copied the concatenated address and paste special into another cell. Now, when I copy this value into Notepad, then copy and paste it back into an Excel cell, I get the value I’m looking for. But if I just copy and paste using Excel, it just repeats the target filepath and et cetera.

    Is there a special kind of paste I need to do so it’ll work like when I paste it into Notepad and then copy it back to Excel?

  2. Thanks for your help! Today, I was able to adapt your examples to simplify my work and save some time everyweek from now on.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo