Excel VBA: Copy Cell Value and Paste to Another Cell

In this article, I’ll show you how you can copy the value of a cell and paste it to another cell using VBA in Excel. You’ll learn to copy the value of both a single cell and a range of cells using VBA.

Excel VBA: Copy Cell Value and Paste to Another Cell (Quick View)

Sub Copy_Single_Cell_Value()

Copy_Sheet = "Sheet1"
Copy_Cell = "B4"

Paste_Sheet = "Sheet2"
Paste_Cell = "B4"

Worksheets(Copy_Sheet).Range(Copy_Cell).Copy

Worksheets(Paste_Sheet).Range(Paste_Cell).PasteSpecial Paste:=xlPasteAll

End Sub

Download Practice Workbook

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

3 Suitable Methods to Copy Cell Value and Paste to Another Cell Using Excel VBA

Here we’ve got a data set in a worksheet called Sheet1 in an Excel workbook that contains the customer names, contact numbers, and the email addresses of some customers of a bank in the range B3:D13.

Our objective today is to copy a cell value and paste it to another cell from this data set using VBA.

1. Copy a Single Cell and Paste It to Another Cell

First of all, we’ll copy the value of a single cell and paste it into another cell. We can paste it into the same worksheet or into a different worksheet.

For example, let’s copy the value of cell B4 (Boris Pasternak) and paste it into cell B4 of Sheet2.

The VBA code will be:

⧭ VBA Code:

Sub Copy_Single_Cell_Value()

Copy_Sheet = "Sheet1"
Copy_Cell = "B4"

Paste_Sheet = "Sheet2"
Paste_Cell = "B4"

Worksheets(Copy_Sheet).Range(Copy_Cell).Copy

Worksheets(Paste_Sheet).Range(Paste_Cell).PasteSpecial Paste:=xlPasteAll

End Sub

⧭ Output:

Run this code after changing the inputs. It’ll copy the value from cell B4 of Sheet1 (Boris Pasternak) to cell B4 of Sheet2.

Read More: How to Copy and Paste in Excel Using VBA (7 Methods)

Similar Readings

• Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
• How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
• Excel Formula to Copy Cell value to Another Cell
• Use Paste Special Command in Excel (5 Suitable Ways)
• How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)

2. Copy an Adjacent Range of Cells and Paste It to Another Range

Next. we’ll copy an adjacent range of cells and paste it to another range.

Let’s copy the range B3:D13 of Sheet1 and paste it into the range B3:D13 of Sheet2.

The VBA code will be:

⧭ VBA Code:

Sub Copy_Adjacent_Range_of_Cells()

Copy_Sheet = "Sheet1"
Copy_Range = "B3:D13"

Paste_Sheet = "Sheet2"
Paste_Range = "B3:D13"

Worksheets(Copy_Sheet).Range(Copy_Range).Copy

Worksheets(Paste_Sheet).Range(Paste_Range).PasteSpecial Paste:=xlPasteAll

End Sub

⧭ Output:

Run this code after changing the inputs. It’ll copy the values from the range B3:D13 of Sheet1 (Boris Pasternak) to the range B3:D13 of Sheet2.

Read More: Excel Formula to Copy Cell value to Another Cell

3. Copy a Non-Adjacent Range of Cells and Paste It to Another Range Using Excel VBA

Finally, we’ll copy a non-adjacent range of cells and paste it into another range of cells.

For example, let’s try to copy the range B3:B13 and D3:D13 of Sheet1 to the range B3:C13 of Sheet2.

To join non-adjacent cells of a worksheet into a common range, we’ll use the Union method of VBA.

The VBA code will be:

⧭ VBA Code:

Sub Copy_Non_Adjacent_Range_of_Cells()

Copy_Sheet = "Sheet1"
Copy_Range = Array("B3:B13", "D3:D13")

Paste_Sheet = "Sheet2"
Paste_Range = "B3:C13"

Set Copy_Range2 = Worksheets(Copy_Sheet).Range(Copy_Range(0))

For i = 1 To UBound(Copy_Range)
    Set Copy_Range2 = Union(Copy_Range2, Worksheets(Copy_Sheet).Range(Copy_Range(i)))
Next i

Copy_Range2.Copy

Worksheets(Paste_Sheet).Range(Paste_Range).PasteSpecial Paste:=xlPasteAll

End Sub

⧭ Output:

Change the inputs and run this code. It’ll copy the values from the range B3:B13 and D3:D13 of Sheet1 to the range B3:C13 of Sheet2.

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

Things to Remember

In this article, we’ve used the xlPasteAll property of the PasteSpecial method of VBA for pasting. Along with it, there are 11 more properties of the PasteSpecial method. You can have a glance at them if you are interested.

Conclusion

Therefore, this is the process to copy a cell value and paste it into another cell using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• Macro to Copy Specific Columns from One Worksheet to Another in Excel
• Excel Formula to Copy Text from One Cell to Another Sheet
• How to Copy Unique Values to Another Worksheet in Excel (5 Methods)
• Paste Link and Transpose in Excel (8 Quick Ways)
• Macro to Copy Data from One Workbook to Another Based on Criteria
• How to Copy and Paste Thousands of Rows in Excel (3 Ways)‌
• Copy and Paste Multiple Cells in Excel (7 Quick Ways)