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

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

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.

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.

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.

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.

Download Practice Workbook

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

Copy Cell Value and Paste to Another Cell.xlsm

Conclusion

Therefore, this is the process of copying a cell value and pasting it into another cell using VBA in Excel. Do you have any questions? Feel free to ask us.