How to Copy a Cell Value and Paste it into Another Cell in Excel VBA - 3 Methods

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

The sample dataset contains names, contact numbers, and the email addresses in Sheet1.

Method 1 – Copy a Single Cell and Paste It to Another Cell

Use the 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

Run the code. It’ll copy the value in B4 of Sheet1 (Boris Pasternak) to B4 of Sheet2.

Method 2 – Copy an Adjacent Range of Cells and Paste It into Another Range

Use the 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

Run the code. It’ll copy the values in B3:D13 of Sheet1 (Boris Pasternak) to B3:D13 of Sheet2.

Method 3 – Copy a Non-Adjacent Range of Cells and Paste It into Another Range Using Excel VBA

Use the 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