Transfer Data from One Excel Worksheet to Another Automatically

Get FREE Advanced Excel Exercises with Solutions!

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.

transfer data from one excel worksheet to another automatically


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.

Apply Paste Link Option to transfer data from one excel worksheet to another automatically

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

Destination Worksheet where data will be transfered automatically from the dataset worksheet

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.

📌 Steps:

  • 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.

Apply Paste Link Option to transfer data from one excel worksheet to another Within Same Workbook automatically

  • 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.

Read More: How to Automatically Update One Worksheet from Another Sheet in Excel


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.

📌 Steps:

  • 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.

Apply Paste Link Option to transfer data from one excel worksheet to another Across Different Workbooks automatically

  • 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.

📌 Steps:

  • In the Worksheet Reference worksheet, select Cell D5 and put an Equal (=) Do not press Enter yet.

Using Worksheet Reference to transfer data from one excel worksheet to another Across Different Workbooks automatically

  • 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.

Read More: How to Reference Cell in Another Excel Sheet Based on Cell Value


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.

📌 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.

Insert Plus (+) Symbol to transfer data from one excel worksheet to another automatically

  • 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.

Read More: How to Link Excel Data Across Multiple Sheets


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.

Automatically Send Data to Another Worksheet Through Excel VBA

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.

Change Properties to change the caption in order to transfer data from one worksheet to another automatically

  • 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.

Insert VBA code to the Command button in order to transfer data from one worksheet to another automatically

  • The VBA window will appear.
  • In the VBA module, paste the following codes:
Option Explicit
Private Sub CommandButton1_Click()
Dim Smartphone As String, Price As String
Worksheets("VBA_1").Select
Smartphone = Range("B5")
Price = Range("C5")
Worksheets("VBA_2").Select
Worksheets("VBA_2").Range("B4").Select
If Worksheets("VBA_2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("VBA_2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Smartphone
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Price
Worksheets("VBA_1").Select
Worksheets("VBA_1").Range("B5:C5").ClearContents
End Sub

  • 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.

Read More: How to Transfer Data from One Sheet to Another in Excel Using Macros


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Conclusion

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.


Related Articles


<< Go Back To Excel Link Sheets | Linking in Excel | 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.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

1 Comment
  1. Can this final method (#4) be easily expanded to incorporate more columns and rows? Would you be able to comment on what exactly the code is implementing?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo