While working with multiple sheets in Excel, it’s very usual to feel the necessity to transfer data from one worksheet to another. There are easy and simple ways to meet this objective. In this article, you’ll learn how you can transfer data from one Excel worksheet to another automatically through 5 suitable methods with short explanations.
Table of Contents
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Automatically Transfer Data Across Worksheets.xlsm
5 Quick Ways to Transfer Data from One Excel Worksheet to Another Automatically
Here, for demonstration purposes, we used the dataset below, where we have various smartphones and their information listed. For avoiding any compatibility issues, use Microsoft Excel 365 edition.
1. Apply Paste Link Option in Excel for Transferring Data Automatically
In the following picture, the Dataset worksheet is representing the specifications of a number of smartphone models.
And here is Paste Link worksheet where three columns from the Dataset sheet have been extracted. The Price column has not been copied yet as we’ll show different methods here to pull out the price list from the first sheet. We must maintain some rules that will auto-update the price column if any change is made in the corresponding column in the first sheet (Dataset).
We can link these two worksheets so that data in one worksheet (Paste Link) will be auto-populated based on another worksheet (Dataset).
1.1 Within Same Workbook
Here, the data are going to be transferred from one worksheet to another worksheet within the same workbook. The data will also be going to be updated whenever the source data changes.
Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
1.2 Across Different Workbooks
Until now, we have seen that we can transfer data from one worksheet to another worksheet. But those transfers have been done inside the same workbook. But here, we are going to transfer data from one workbook to another workbook.
2. Transfer Data from One Sheet to Another by Using Worksheet Reference
Now we’ll apply another method where we won’t have to copy and paste anything from one worksheet to another. Rather, we’ll use the cell reference(s) from another worksheet to auto-populate data on other worksheet’s cells.
Read More: How Excel Pull Data From Another Sheet Based on Criteria
3. Insert Plus (+) Symbol to Move Data to Another Worksheet Automatically
In this section, we’ll apply an alternative formula starting with a Plus symbol (+) instead of an Equal sign (=). Let’s go through the following steps.
Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
4. Automatically Send Data to Another Worksheet Through Excel VBA
In our last section, we’ll apply VBA codes to transfer data from VBA_1 to VBA_2. In the picture below, VBA_1 contains the values in B4 and C4 respectively. What we’ll do here is type a smartphone model and its price in B5 and C5 first. Then we’ll press a customized button that will transfer the input data from VBA_1 to VBA_2.
And here is the VBA_2 worksheet, where the list of smartphone models and the corresponding prices will be auto-populated from the VBA_1 worksheet.
So, we can move to the necessary steps now that will execute the Macro properly.
Step 1: Insert Command Button
The default button caption needs to be changed to a custom one for better clarity.
Step 2: Assign Macro to the Button
We can now assign a VBA code to the command button.
Step 3: Execute the Code to Transfer Data
We can now move the data from one worksheet to another worksheet by pressing the button.
Read More: How to Transfer Data from One Sheet to another in Excel Using Macros
I hope all these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you must transfer data from one worksheet to another automatically. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.