Transfer Data from One Excel Worksheet to Another Automatically

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 a number of easy and simple ways to meet this objective. In this article, you’ll learn how you can transfer data to another worksheet with suitable examples and short explanations.


Download Practice Workbook

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


4 Quick Ways to Transfer Data from One Worksheet to Another Automatically in Excel

1. Use Paste Link to Transfer Data from Another Worksheet Automatically

In the following picture, Sheet1 is representing some specifications of a number of smartphone models.

Use Paste Link to Transfer Data from Another Worksheet Automatically

And here is Sheet2 where only three columns from the first 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 have to maintain some rules that will auto-update the price column if any change is made in the corresponding column in the first sheet (Sheet1).

Use Paste Link to Transfer Data from Another Worksheet Automatically

Now let’s have a look at how we can link between these two worksheets so that data in one worksheet (Sheet2) will be auto-populated based on another worksheet (Sheet1).

📌 Step 1:

➤ From Sheet1, select the range of cells (F5:F14) containing the prices of the smartphones.

➤ Press CTRL+C to copy the selected range of cells.

Use Paste Link to Transfer Data from Another Worksheet Automatically

📌 Step 2:

➤ Go to Sheet2 now.

➤ Select the first output cell in the Price column.

➤ Right-click the button of your mouse and choose the Paste Link option as marked within a red square in the following screenshot.

Use Paste Link to Transfer Data from Another Worksheet Automatically

And the Price column is now complete with the extracted data from the first sheet (Sheet1).

Use Paste Link to Transfer Data from Another Worksheet Automatically

Now we’ll see how a change of data in the primary worksheet (Sheet1) auto-populates data in the second worksheet (Sheet2).

📌 Step 3:

➤ In Sheet1, change the price value of any smartphone model.

➤ Press Enter and go to Sheet2.

Use Paste Link to Transfer Data from Another Worksheet Automatically

And you’ll find the updated price of the corresponding smartphone in Sheet2. This is how we can easily link between two or multiple worksheets to transfer data.

Use Paste Link to Transfer Data from Another Worksheet Automatically


2. Transfer Data Automatically by Using Worksheet Reference in Excel

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.

📌 Step 1:

➤ In Sheet2, select Cell D5 and put an Equal (=) sign.

Transfer Data Automatically by Using Worksheet Reference in Excel

📌 Step 2:

➤ Go to Sheet1.

➤ Select the range of cells (F5:F13) containing the prices of all smartphone models.

➤ Press Enter.

Transfer Data Automatically by Using Worksheet Reference in Excel

Now in Sheet2, you’ll find an array of prices in Column D ranging from D5 to D14. If you change any data in the Price column in Sheet1, you’ll also see the updated price of the corresponding item in Sheet2 right away.

Transfer Data Automatically by Using Worksheet Reference in Excel

Read more: How Excel Pull Data From Another Sheet Based on Criteria


3. Use Plus (+) Symbol to Transfer 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.

📌 Step 1:

➤ Select the output Cell D5 in Sheet2.

➤ Start typing and input a Plus symbol (+) there only. Don’t press Enter now.

➤ Drag your mouse pointer to the Sheet1 tab.

➤ Right-click your mouse button and you’ll be redirected to Sheet1.

Use Plus (+) Symbol to Transfer Data to Another Worksheet Automatically

📌 Step 2:

➤ In Sheet1, now select the range of cells (F5:F14) containing the prices of all devices.

➤ Press Enter.

Use Plus (+) Symbol to Transfer Data to Another Worksheet Automatically

Like in the following screenshot, you’ll find all prices under the Price column in Sheet2. And if you change a price of a smartphone device in Sheet1, it’ll automatically update the corresponding price in Sheet2 immediately.

Use Plus (+) Symbol to Transfer Data to Another Worksheet Automatically


4. Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

In our last section, we’ll apply VBA codes to transfer data from Sheet1 to Sheet2. In the picture below, Sheet1 contains the Smartphone and Price tabs 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 Sheet1 to Sheet2.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

And here is the Sheet2 where the list of smartphone models and the corresponding prices will be auto-populated from Sheet1.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

So, we can move to the necessary steps now that will execute the Macro properly.

📌 Step 1:

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

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 2:

➤ Now draw the rectangle according to your preferred size. And you’ll see the command button as shown in the following screenshot.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 3:

➤ Right-click the mouse button now.

➤ Select the option Properties.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 4:

➤ In the Caption box, assign the button name, let’s say it’s ‘Transfer to Sheet2’.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 5:

➤ Right-click on the Sheet tab and choose View Code.

The VBA window will appear.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 6:

➤ In the VBA module, paste the following codes:

Option Explicit
Private Sub CommandButton1_Click()
Dim Smartphone As String, Price As String
Worksheets("Sheet1").Select
Smartphone = Range("B5")
Price = Range("C5")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("B4").Select
If Worksheets("Sheet2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Smartphone
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Price
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("B5:C5").ClearContents
End Sub

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 7:

➤ Now return to your Sheet1.

➤ Type the name of a smartphone model and its price in the corresponding input cells.

➤ Click on the button ‘Transfer to Sheet2’.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

And you’ll see that the input data are gone from Sheet1.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

Now switch to Sheet2 and you’ll find your input data there under the corresponding headers.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

📌 Step 8:

➤ Let’s go to Sheet1 once again.

➤ Type the name of another smartphone device and its price.

➤ Press the command button on the right.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel

So, we have just transferred the second input data also to Sheet2. In this way, we can input more and more data in Sheet1 and auto-update Sheet2 with the input data from Sheet1 every time.

Embed VBA Macro to Transfer Data Automatically to Another Worksheet in Excel


Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to transfer data from one worksheet to another. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Further Readings

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

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

ExcelDemy
Logo