Alok Paul
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.
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.
- 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.
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 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.
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.
- 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.
- 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.
- 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).
- 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.
- 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.
- 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
- 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.
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.
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.
