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 4 suitable methods with short explanations.
Transfer Data from One Excel Worksheet to Another Automatically: 4 Quick Ways
Here, for demonstration purposes, we used the dataset below, where we have various smartphones and their information listed. To avoid 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 updated whenever the source data changes.
- From the Dataset worksheet, select the range of cells (D5:D10) containing the prices of the smartphones, and right-click on the mouse.
- Select Copy from the context menu to copy the selected range of cells.
- Go to the Paste Link worksheet now.
- Select the first output cell in the Price column.
- Right-click on your mouse and choose the Paste Link option as marked within a red square in the following screenshot.
- After selecting the Paste Link, you will notice that the Price column is now complete with the extracted data from the first sheet (Dataset).
- Now we’ll see how a change of data in the primary worksheet (Dataset) auto-populates data in the second worksheet (Paste Link).
- In the Dataset worksheet, change the price value of any smartphone model. For example, change the cell value in cell D8 from 850 to 750.
- Press Enter and go to the Paste Link worksheet.
- And you’ll find the updated price of the corresponding smartphone in the Paste Link worksheet is also changed from 850 to 750. This is how we can easily link between two or multiple worksheets to transfer data.
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.
- Firstly, we must open both workbooks at the same time.
- The below dataset is the dataset from where we will transfer the data to another worksheet in a separate workbook.
- The Price column values are going to be transferred to the new workbook.
- On the other hand, we have only the Brand and Price columns in the destination dataset.
- To begin with, we need to select the range of cells D5:D10 and then right-click on the mouse.
- Then from the context menu, select Copy to copy the range of cells to the clipboard.
- Move to the destination workbook and then select cell C5.
- Then right-click on the mouse and from the context menu click on the Paste Link icon.
- Clicking the Paste Link icon will paste and link the data saved in the range of cell in D5:D10 (Source Workbook) in the range of cell C5:C10 (Destination Workbook).
- Now if we change any data in the source dataset, then the data is also going to be updated with it.
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.
- In the Worksheet Reference worksheet, select Cell D5 and put an Equal (=) Do not press Enter yet.
- Go to the Dataset worksheet.
- Select the range of cells (D5:D10) containing the prices of all smartphone models.
- Press Enter.
- In the Worksheet Reference worksheet, you’ll find an array of prices in column D ranging from D5 to D10.
- If you change any data in the Price column in Dataset worksheet, you’ll also see the updated price of the corresponding item in Worksheet Reference right away.
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.
- Select the output cell D5 in the Plus Symbol worksheet.
- Start typing and input a Plus symbol (+) there only. Don’t press Enter now.
- Drag your mouse pointer to the Dataset sheet.
- Right-click your mouse button and you’ll be redirected to the Dataset worksheet.
- In the Dataset worksheet, select the range of cells (D5:D10) containing the prices of all devices.
- Press Enter.
- After pressing Enter, you’ll find all prices under the Price column in the Plus Symbol worksheet. And if you change the price of a smartphone device in the Dataset worksheet, it’ll automatically update the corresponding price in the Plus Symbol worksheet immediately.
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
- Go to the Developer ribbon first.
- From the Insert drop-down, select the first command button shown in a rectangular shape from the ActiveX Controls section.
- Now draw the rectangle according to your preferred size using the arrow shown in the image in the picture.
- And you’ll see the command button as shown in the following screenshot.
- To change the caption, right-click the mouse button now.
- Select the option Properties.
The default button caption needs to be changed to a custom one for better clarity.
- In the Caption box, assign the button name, let’s say it’s ‘Transfer to VBA_2 Sheet.
- Press Enter or click on the cross in the corner of the box.
- After this, we will notice that the caption is now Transfer to VBA_2 Sheet.
Step 2: Assign Macro to the Button
We can now assign a VBA code to the command button.
- Right-click on the Command Button again and choose View Code from the context menu.
- The VBA window will appear.
- In the VBA module, paste the following codes:
Private Sub CommandButton1_Click()
Dim Smartphone As String, Price As String
Smartphone = Range("B5")
Price = Range("C5")
If Worksheets("VBA_2").Range("B4").Offset(1, 0) <> "" Then
ActiveCell.Value = Smartphone
ActiveCell.Value = Price
- Now click on the Save icon and return to your VBA_1 worksheet.
Step 3: Execute the Code to Transfer Data
We can now move the data from one worksheet to another worksheet by pressing the button.
- Type the name of a smartphone model and its price in the corresponding input cells.
- Click on the button Transfer to VBA_2 command button.
- And you’ll see that the input data is absent from the VBA_1 worksheet and the data is now in the VBA_2 dataset.
- Now switch to VBA_2 worksheet and you’ll find your input data there under the corresponding headers.
- Let’s go to the VBA_1 worksheet once again.
- Type the name of another smartphone device and its price.
- Press the command button on the right.
- Switch back to the VBA_2 worksheet and then you will notice that the new information is now right below the existing dataset.
- So, we have just transferred the second input data also to VBA_2. In this way, we can input increased data in VBA_1 and auto-update VBA_2 with the input data from VBA_1 every time.
- Like in the below image you can see that we get all the smartphone names, and their data are serially in the VBA_2 worksheet.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
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 appreciated.
- Transfer Specific Data from One Worksheet to Another for Reports
- Linking Excel Sheets to a Summary Page
- How to Make Excel Look Like an Application
- Best Practices for Linking Excel Spreadsheets