How to Copy and Paste Values in VBA

1. Macros in Excel
2. Macros’ Working in Microsoft Excel
3. Demonstration of VBA Copy Paste Values

This tutorial illustrates how we can copy the values of one column to another within the same sheet or a different sheet in Excel using VBA.

Macros in Excel

It has become easier to do repeated Excel tasks using Macros written in VBA. We can write Macros to do a specific job, and the user can run them whenever required.

It makes it easier to do complex things several times over and over again. The macros need to be coded once and can be used infinite times. Remember, we should write Macros in such a way that enhances useability.

Try NOT to hardcode values in a Macro. It is ideal for taking inputs from the user to enhance useability.

If you are looking for VBA statements that allow you to copy the values from one column to another in Excel, you must be aware of the definition and usage of a Macro.

Macros in Microsoft Excel are an excellent feature that repeatedly automates your desired tasks. In short, a Macro is an action or a set of activities that the user records to run it frequently.

Macros’ Working in Microsoft Excel

We can write Macros in two ways in Microsoft Excel. Both methods are designed to ensure user feasibility. Here are the two approaches to working with macros in Microsoft Excel:

Approach 1: Record a Macro

The easiest method to perform a task repeatedly with the help of a Macro is by creating and recording one. We can do all of this with the help of some mouse clicks and keystrokes only.

The steps to record a Macro are pretty straightforward. Just follow the instructions mentioned below:

1. Open the View tab in Microsoft Excel. It must show an option for Macros. When you click on the Macros option, it should provide you with the option of viewing the different macros that have already been recorded. In addition, it should also allow you to record a new macro as well.
2. Click the Record Macro option, and a window will appear.
3. Name the Macro and describe it. You can create a shortcut for a specific Macro. For example, setting the shortcut as Ctrl + w will automatically run the Macro whenever this shortcut is called. Of course, this is optional, but it is recommended as a good practice.
4. Record all the keystrokes and mouse clicks that you would like to repeat within the specific Macro and stop recording the Macro as soon as you finish the work.
5. Whenever you run the Macro from the View tab or use the shortcut you defined earlier, it will perform the same task recorded in the Macro.

Record a Macro With Absolute References or Relative References

By default, a Macro is recorded with absolute addresses. Therefore, if you copy values from columnA to columnC, the recorded Macro will always do the same task.

If you record the Macro with relative references, it will act relative to the selected cell—for instance, the Macro records copying values from column A to column C.

If column W is chosen to run the Macro, it will copy values from column W and paste them to column Y. Again, do not forget to use the absolute or relative addressing according to your requirements.

Approach 2: Write a Macro

If you wish to perform particular tasks, such as those requiring input from the users or providing output to the users, you need to write down these Macros.

Recording a Macro does not allow you to perform and generalize specific tasks; hence, you can write a Macro tailored to your needs using VBA in Microsoft Excel.

The instructions for writing a Macro are pretty straightforward. The following steps define the procedure for writing a Macro:

1. Choose the Macro button on the Developer tab in Microsoft Excel.

2. The option opens a Macros dialogue box where you can access all the VBA subroutines or Macro.

3. Click the Visual Basic button to open a Visual Basic Editor.

4. Within this editor, you can write the code for your Macro, edit it, test it, and execute it. Remember, there are no options for choosing absolute and relative addresses while writing a Macro.

   If the VBA code is hard coded with specific values, it will run according to absolute references. If the VBA code is written in general, it will run according to relative references.

Demonstration of VBA Copy Paste Values

If you want to copy values from one column and paste them into another column existing in the same or a different sheet, a simple and easy VBA code can do the trick.

The article shares a subroutine to do the task of copy-pasting values from one column to another using absolute and relative references.

The user itself can write the main function and can include the subroutine mentioned in the article to perform the task of copy-pasting values.

Three solutions have been mentioned below:

Solution 1: Copy Values From Column1 of Sheet1 to Column2 of Sheet2

Example Code:

Public Sub CopyPasteValues() 
    Sheets("Sheet1").Columns(1).Copy
    Sheets("Sheet2").Columns(2).PasteSpecial xlPasteValues
End Sub

The code above creates a subroutine CopyPasteValues() that selects Column1 from Sheet1 and copies it. It then selects Column2 from Sheet2 and pastes the copied values. The xlPasteValues attribute is only used to copy the cells’ values.

Solution 2: Copy Values From ColumnA of Sheet1 to ColumnB of Sheet2

Example Code:

Public Sub CopyPasteValues()
    Sheets("Sheet1").Columns(A).Copy
    Sheets("Sheet2").Columns(B).PasteSpecial xlPasteValues 
End Sub

The code above is similar to the code mentioned in the first solution. It is an alternative to the first solution to give an idea to the user of the multiple options that we can use to access columns in a sheet in Excel.

Solution 3: Subroutine With Relative References to Copy Values Within Specific Range

The third solution shares a subroutine with relative references to copy values from one specific range to another.

Example Code:

Public Sub CopyPasteValues() 
    Dim arr1, arr2, i As Integer 
    arr1 = Array("AC", "AD") 
    arr2 = Array("A", "B") 

    For i = LBound(arr1) To UBound(arr1)
        Sheets("Sheet1").Columns(arr1(i)).Copy
        Sheets("Sheet2").Columns(arr2(i)).PasteSpecial xlPasteValues 
    Next

    Application.CutCopyMode = False
End Sub

The code above creates a subroutine CopyPasteValues() that uses relative addresses in the for loop; therefore, variables are used for it.

• The arr1 is the specific range in Sheet1 that has to be copied.
• The arr2 is the specific range in Sheet2 where we will paste the values.
• The i variable is a counter for the for loop.

The for loop copies the values in the column AC in Sheet1 to the column A in Sheet2 and continues the execution. At the end of the for loop, Application.CutCopyMode = False is written to deselect the specific ranges of values that are selected to copy to the other sheet.

So, we learned that VBA allows users to write Macros in Microsoft Excel to perform frequent tasks quickly. We can record Macros as well as write. It also enables Macros to serve unlimited functionalities in Microsoft Excel.