Transferring data between Excel worksheets is a crucial task especially when you need to handle huge amounts of information. Well, in this blog, I will discuss how to transfer data from one Excel worksheet to another automatically.
So, just go through this blog carefully.
To fix Excel file and recover lost data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Methods to Transfer Data From One Excel Worksheet To Another Automatically
There are many different ways to transfer data from one Excel workbook to another and they are as follows:
Method #1: Automatically Update One Excel Worksheet From Another Sheet
In the MS Excel workbook, we can easily update the data by linking one worksheet to another. This link is known as a dynamic formula that transfers data from one Excel workbook to another automatically.
One Excel workbook is called the source worksheet, where this link carries the worksheet data automatically, and the other workbook is called the destination worksheet which automatically updates the worksheet data and contains the link formula.
The following are the two different points to link the Excel workbook data for the automatic updates.
1) With the use of Copy and Paste option
- In the source worksheet, select and copy the data that you want to link to another worksheet
- Now in the destination worksheet, Paste the data where you have linked the cell source worksheet
- After that choose the Paste Link menu from the Other Paste Options in the Excel workbook
- Save all your work from the source worksheet before closing it
2) Manually enter the formula
- Open your destination worksheet, tap on the cell that has a link formula, and put an equal sign (=) across it
- Now go to the source sheet and tap on the cell which is having data. press Enter from your keyboard and save your tasks.
Note– Always remember one thing the format of the source worksheet and the destination worksheet are the same.
Also Read: Master Spreadsheet Skills: How to Outline Columns and Rows in Excel?
Method #2: Update Excel Spreadsheet With Data From Another Spreadsheet
To update Excel spreadsheets with data from another spreadsheet, just follow the points given below which will be applicable for the Excel version 2019, 2016, 2013, 2010, and 2007.
- First, go to the Data menu
- Select Refresh All option
- Here you have to see when or how the connection is refreshed
- Now click on any cell that contains the connected data
- Again in the Data menu, click on the arrow which is next to Refresh All option and select Connection Properties
- After that in the Usage menu, set the options that you want to change
- On the Usage tab, set any options you want to change.
Note – If the size of the Excel data workbook is large, then I will recommend checking the Enable background refresh menu on a regular basis.
Method #3: How To Copy Data From One Cell To Another In Excel Automatically
To copy data from one cell to another in Excel, just go through the following points given below:
- First, open the source worksheet and the destination worksheet.
- In the source worksheet, navigate to the sheet that you want to move or copy
- Now, click on the Home menu and choose the Format option
- Then, select the Move Or Copy Sheet from the Organize Sheets part
- After that, again in the Home menu choose the Format option in the cells group
- Here in the Move Or Copy dialog option, select the target sheet and Excel will only display the open worksheets in the list
- Else, if you want to copy the worksheet instead of moving it, then kindly make a copy of the Excel workbook before
- Lastly, select the OK button to copy or move the targeted Excel spreadsheet.
Method #4: How To Copy Data From One Sheet To Another In Excel Using Formula
You can copy data from one sheet to another in Excel using formula. Here are the steps to be followed:
- To copy and paste the Excel cell in the present Excel worksheet, for example; copy cell A1 to D5, you can just select the destination cell D5, then enter =A1 and press the Enter key to get the A1 value.
- For copying and pasting cells from one worksheet to another worksheet such as copying cell A1 of Sheet1 to D5 of Sheet2, please select the cell D5 in Sheet2, then enter =Sheet1!A1 and press the Enter key to obtain the value.
Method #5: Copy Data From One Excel Sheet To Another Using Macros/VBA
With the help of macros, you can copy data from one worksheet to another but before this here are some important tips that you must take care of:
- You should keep the file extension correctly in your Excel workbook.
- Your spreadsheet doesn’t need to be macro-enabled to do the task.
- The code that you choose can also be stored in a different worksheet.
- As the codes already specify the details, so there is no need to activate the Excel workbook or cells first.
- Thus, given below is the code for performing this task.
Sub OpenWorkbook()
‘Open a workbook
‘Open method requires full file path to be referenced.
Workbooks.Open “C:\Users\username\Documents\New Data.xlsx”‘Open method has additional parameters
‘Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)End Sub
Sub CloseWorkbook()
‘Close a workbook
Workbooks(“New Data.xlsx”).Close SaveChanges:=True
‘Close method has additional parameters
‘Workbooks.Close(SaveChanges, Filename, RouteWorkbook)End Sub
Recommended Solution: MS Excel Repair & Recovery Tool
When you are performing your work in MS Excel and if by mistake or accidentally you do not save your workbook data or your worksheet gets deleted, then here we have a professional recovery tool for you, i.e. MS Excel Repair & Recovery Tool.
With the help of this tool, you can easily recover all lost data or corrupted Excel files. This is a very useful software to get back all types of MS Excel files with ease.
Steps to Utilize MS Excel Repair & Recovery Tool:
Also Read: How to Combine First and Last Name in Excel?- (4 Ways)
Related FAQs:
How Do I Link a Cell to Another Sheet in Excel?
To link a cell to another Excel sheet, follow these steps:
- Select and right-click on any cell >> from a menu and select ‘Link’.
- Choose 'place in this document' in a window that opens >> Select the tab you need the link to go to.
What Is the Formula to Automatically Copy Cells from Another Sheet?
You can use the VLOOKUP Excel formula to automatically copy cells from another sheet.
How Do You Copy Excel Sheet to Another Sheet with Same Format Automatically?
If you want to copy the same formatting from one Excel sheet to another, including row height, column width, and cell color, you can use the ‘Format Painter’ option.
What Is the Shortcut for Transfer Between Sheets in Excel?
Hold the Ctrl key & press a PageUp button on the keyboard to transfer the sheet to the right, or the PageDown button to transfer the sheet to the left.
Verdict Time
Automating the transfer of data between two Excel sheets not only saves time but also ensures accuracy & consistency. Above, I have discussed how to transfer data from one Excel worksheet to another automatically.
Use these methods to efficiently manage your data and simplify your tasks.
Priyanka Sahu
Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.