How to Automatically Copy Data from One Cell to Another in Excel (4 Methods)

Below is a dataset of a company’s Weekly Sales Report.

Method 1 – Using Cell Reference

1.1 Copying Data to the Same Sheet

Steps:

Select the cell where we want to copy data.
Press “=” and type the Cell Reference we want to Copy or select that cell. (i.e., C5)

Press ENTER. The data is copied from cell C5 to cell D5.

Drag down the Fill Handle tool to fill the other cells – as shown below.

1.2 Copying Data From Different Sheets

Steps:

Select a cell in the Cell reference_2 sheet to copy the cell’s data (i.e., C5).
Press “=” and type the source sheet name.
Enter an exclamatory(!) sign and the cell name.
Press ENTER. You will get a copy of the cell of the mentioned sheet. Apply the same process to the other cells.

Copying Data to Different Sheets in an Alternative Way:

Steps:

Select a cell in the Cell reference_2 sheet to copy the cell’s data (i.e., C5).
Press “=”.
Select the source sheet from the sheet name section.
Select the cell you want to copy.
Press ENTER to get the result on the destination sheet as shown below.

GIF to to copy data from one cell to another in excel automatically

Drag down for other cells to get the following result.

Method 2 – Applying the Paste Special Command

2.1 Incorporating the Paste Special Dialog Box

Steps:

Select the cell you want to copy (i.e., C5).
Press Ctrl+C to copy the cell.

Go to the destination cell and press CTRL + ALT + V. It will open the Paste Special dialog box.
From the Paste Special box, choose Paste Link.
Press OK.

The link of the source cell will be copied to the destination cell.

2.2 Applying the Clipboard Paste Option

Steps:

Select the cell you want to copy (i.e., C5).
From Ribbon, go to Home.
Go to the Copy command. Select Copy from the drop-down.

Select a cell to paste the data (i.e., D5).
Go to the Paste command. Select Paste Link (N).

The cell link to the source cell will be stored here.

If the source data is changed, the destination data will change automatically.

Method 3 – Copying Data from One Workbook to Another

Steps:

Open two workbooks to copy data.
Copy the dataset from the Book 1 workbook.

Select a cell in the destination workbook.
Enter “=” sign.
Select the cell from the source workbook.

Press ENTER, and the data from the source workbook is copied to the destination workbook.

Method 4 – Incorporating VBA Macros

Steps:

Go to the Developer tab >> choose Visual Basic.

Go to the Insert tab >> Module >> Module1.

Enter the following VBA code in the Module box:

Sub Excel_Paste_Special_1()
Dim Copy_Cell As Range, Paste_Cell As Range
xTitleId = "VBA"
Set Copy_Cell = Application.Selection
Set Copy_Cell = Application.InputBox("Select Range to Copy :", xTitleId, Copy_Cell.Address, Type:=8)
Set Paste_Cell = Application.InputBox("Paste to any blank cell:", xTitleId, Type:=8)
Copy_Cell.Copy
Paste_Cell.Parent.Activate
Paste_Cell.PasteSpecial xlPasteValuesAndNumberFormats
Paste_Cell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub