How to Paste values using VBA in Excel

By SpreadCheaters
Page last updated: 23/11/2022 | Next review date: 23/11/2024
close

Author bio


SpreadCheaters
View More north_east

Microsoft Office provides support for programmers who like to automate their tasks through programming. Visual Basic for Applications also known as VBA is the programming language for Microsoft Office Applications, i.e., Excel, Word, PowerPoint, Access etc. We can write simple to complex pieces of code to automate various functions through VBA.

In this tutorial we’ll learn how to paste values using the simple VBA code. We will paste values within the same sheet and then another sheet of the same workbook as well.

Let’s look at our workbook first. This will help us to understand the code better. Our workbook contains two sheets. The first sheet named “Data Sheet” (source sheet) contains the data and formulas, the second sheet is “Paste Values only” where we’ll paste the values through VBA code.

Method 1 Paste values in the Same Sheet

Step 1 – Open the VBA Editor by pressing ALT+F11 or through Developer Tab

  • In Excel’s main menu, click on the  Developer tab and then click on Visual Basic.

Step 2 – Insert the Module and write the code

  • The previous step will open the VBA editor. In this editor’s main menu tabs, click on Insert and then choose Module. This will insert a module named Module1 by default and open a white blank space to write the code in to it.
  • This will insert a blank module in the editor where we will insert the code to copy and paste values from “Data Sheet” to the same sheet again but in a different location i.e., cell A12. Now copy the code snippet named “Paste values within same sheet” available at the end of the tutorial and paste it in the blank white space of Module1 in the VB editor.

We’ll use Range.Copy & Range.PasteSpecial methods to achieve our goal. Further in the PasteSpecial method we will choose xlPasteValues option which will copy the values only from the selected range and then paste them at another location in the same sheet. 

The code snippet is available in the last section of the tutorial.

Step 3 – Run the VBA code to paste values in the same sheet

  • After pasting the code, click on the play button available in the toolbar at the top. 
  • This will run the code and paste the values in the same sheet as shown below;

Method 2 Paste values in Another Sheet of Same Workbook

To paste the data values in another sheet of the same workbook, repeat the first step from the previous method. 

Step 2 – Insert the Module and write the code

  • The previous step will open the VBA editor. In this editor’s main menu tabs, click on Insert and then choose Module. This will insert a module named Module2 by default and open a white blank space to write the code into it.
  • This will insert a blank module in the editor where we will insert the code to copy and paste values from “Data Sheet” to the same sheet again but in a different location i.e., cell A12. Now copy the code snippet named “Paste values in another sheet” available at the end of the tutorial and paste it in the blank white space of Module2 in the VB editor.

We’ll use Range.Copy & Range.PasteSpecial methods to achieve our goal. Further in the PasteSpecial method we will choose xlPasteValues option which will copy the values only from the selected range and then paste them at another location in another sheet.

Step 3 – Run the VBA code to paste values in the same sheet

  • After pasting the code, click on the play button available in the toolbar at the top. 
  • This will run the code and paste the values in another sheet as shown below;

The Code Snippet for “Paste values within same sheet”

This is the code used in the first part of this tutorial.

Here the sub procedure Paste_Range_with_Values_To_Same_Sheet() is the main procedure doing all the work. 

The first highlighted line Range(“A2:G4”).Copy copies the data from the range “A2:G4” of the active sheet. As we are going to copy and paste the data in the same sheet so we don’t need to mention the sheet names before range. Excel automatically understands that the code is referring to the active sheet’s data range.

Then the second highlighted line 

Range(“A12”).PasteSpecial Paste:=xlPasteValues,_ 

Operation:=xlNone, SkipBlanks:=False, Transpose:=False

pastes the copied data in the same sheet at cell location A12. We used the Range.PasteSpecial method along with Paste Type xlPasteValues which tells Excel to paste the copied data with values only.

The Code Snippet for “Paste values in another sheet”

This is the code used in the first part of this tutorial.

Here the sub procedure Paste_Range_with_ValuesOnly_To_Another_Sheet() is the main procedure doing all the work. 

The first highlighted line Sheets(“Data Sheet”).Range(“A2:G4”).Copy copies the data from the range “A2:G4” of Data sheet (source sheet). As we are going to copy and paste the data in another sheet so we need to mention the sheet names before range. This way Excel will understand that we are referring to the data ranges from a particular sheet.

Then the second highlighted line 

Sheets(“Paste Values”).Range(“A2”).PasteSpecial Paste:=xlPasteValues,_ 

Operation:=xlNone, SkipBlanks:=False, Transpose:=False

pastes the copied data in another sheet at cell location A2. We used the  Range.PasteSpecial method along with Paste Type xlPasteValues which tells Excel to paste the copied data with values only.