How to Automatically Copy Data from One Cell to Another in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Excel, it’s a very common practice to copy data from one cell to another automatically. It may be from one worksheet to another sheet or even a workbook. It is needed by almost every user who uses Excel. Today we are going to discuss several methods to copy data in Excel automatically. But the copy process is not automatic in Excel. We will discuss this topic in detail too. We also insert the VBA macros for copying and pasting data from one cell to another in excel. So, let’s get started.


How to Automatically Copy Data from One Cell to Another in Excel: 4 Quick Methods

To copy the data you need to make a dataset first. We have taken a dataset of the Weekly Sales Report of a company. Now we will copy the data to another cell.

Dataset

Not to mention, we have used the Microsoft Excel 365 version. You may use any other version at your convenience.


1. Using Cell Reference to Copy Data from One Cell to Another

We can copy data using cell references in the same sheet and different sheets. Here data will change as reference data changes automatically.


1.1 Copying Data to Same Sheet

We can copy data in the same sheet using cell references. The process is described below.

📌 Steps:

  • Firstly, select the cell where we want to copy data.
  • Secondly, press “=” and type the Cell Reference we want to Copy or select that cell. Here we will copy the C5.

Copying Data to Same Sheet to another cell automatically in excel

  • Then press ENTER and see that data is copied from cell C5 to our destination cell D5.

Similarly, drag down the Fill Handle tool for other cells to get the below output.

Fill handle tool to copy data from one cell to another in excel automatically


1.2 Copying Data From Different Sheets

Here we will copy data from the Dataset sheet to the Cell reference_2 sheet.

📌 Steps:

  • Firstly, select a cell in the Cell reference_2 sheet to copy data.
  • Secondly, press “=” and type the source sheet name.
  • Then, put an exclamatory(!) sign and type the cell name.
  • Finally, press ENTER we will get a copy of the cell of the mentioned sheet. Apply the same process to other cells.

Copying Data From Different Sheets to copy data from one cell to another in excel automatically


Copying Data to Different Sheets in Alternative way:

We can also do this using a different technique.

📌 Steps:

  • Firstly, select a cell in the Cell reference_2 sheet to copy data.
  • Consequently, press “=” on the keyboard.
  • Subsequently, select the source sheet from the sheet name section.
  • Now select the cell we want to copy.
  • Finally, press ENTER and we will our result on the destination sheet. See the below GIF for better visualization.
GIF to to copy data from one cell to another in excel automatically

Lastly, drag down for other cells to get the following output.


2. Applying Paste Special Command in Excel

You can use the Paste Special command to copy data from one cell to another in Excel. It is very easy to use and also it is a time-saving task also. The Paste Special command can be gotten in several ways. We have discussed 2 ways here.


2.1 Incorporating Paste Special Dialog Box

We can copy data using Paste Special from the mouse shortcut.

📌 Steps:

  • Firstly, select the cell we want to copy.
  • Press Ctrl+C and copy the cell.

Using paste special feature to copy data from one cell to another in excel automatically

  • Now, go to the destination cell and press CTRL + ALT + V. It will open the Paste Special dialog box.
  • Eventually, from the Paste Special box. Choose Paste Link.
  • After that, hit OK.

Paste Special Dialog box

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


2.2 Applying Clipboard Paste Option

Now, if we change the reference cell then the destination cell will also change.

We can copy data using Paste Special from the Clipboard group of commands.

📌 Steps:

  • Firstly, select the cell we want to copy.
  • Then, from Ribbon go to Home.
  • Subsequently, go to Copy command.
  • Eventually, select Copy from the drop-down.

Clipboard paste option to copy data from one cell to another in excel automatically

  • Apparently, select a cell to paste the data.
  • Then, now go to the Paste command.
  • Additionally, from the Paste drop-down select Paste Link (N).

Paste Link to copy data from one cell to another in excel automatically

  • After selecting Paste Link(N), the link cell link of the source cell will be stored here.

In this method, if the source data change the destination data will change automatically.


3. Copying Data from One Workbook to Another

We can also copy a cell from one workbook to another workbook.

📌 Steps:

  • At first, two workbooks are taken to copy data.
  • After that, we will copy it from the Book 1 workbook.

Copying Data one workbook to another

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

  • Press ENTER we will see that data from the source workbook is copied to the destination workbook.


4. Incorporating VBA Macros

You can use the VBA macros to copy and paste the data to another cell. VBA makes this process quick and automatically copies and pastes the data into another cell. Follow the below steps to use the VBA code.

📌 Steps:

  • Firstly, navigate to the Developer tab >> choose Visual Basic.

Incorporating VBA macros to copy data from one cell to another in excel automatically

  • Sequentially, go to the Insert tab >> Module >> Module1.

  • Consequently, write up the 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

VBA code to copy data from one cell to another in excel automatically

  • Subsequently, run the code with the F5 key and you will get an InputBox to Select Range to Copy for copying the range. Hit OK.

  • Then, select the range of cells where you want to paste the cells. Here, we paste the data in $D$5:$D$10 cells.

Paste Dialog box

Finally, hit OK and you will be able to paste the data like the image below.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. And these are some easy methods to copy data from one cell to another in excel automatically in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.


<< Go Back to Copy Cell Value | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo