How do i automatically copy data between sheets in excel?

You've got data in one sheet in your spreadsheet, and want to use it in another sheet. You could copy it—but then, when you change the data in one sheet, you'll have to change it in the other sheet, too.

There's a better option—link your spreadsheet cells to keep the same data in each place. Here's how in Google Sheets (and the same trick works in Excel, Numbers, Quip, and other popular spreadsheet apps):

Link Spreadsheet Cells with !

To reference a cell from one sheet in another, all you need to know is the sheet's name and the cell's name. Link them together with an exclamation mark.

Say your sheet's name is "Names", and you need the data from cell B3. Just enter =Names!B3 in any cell, and you'll get the data from that cell in your new sheet.

Or, there's an easier option. Type = in your cell, then click the other sheet and select the cell you want, and press enter. That'll type the function for you.

Now, if you change the data in the original B3 cell in the Names sheet, the data will update everywhere you've referenced that cell.

Need to calculate values from that cell? Just type the rest of your function as normal. If Names!B3 has the value 3 and you type =Names!B3*3, you'll get the result 9 in your new cell as you'd expect.

Connect Sheets in Different Spreadsheets

Have cells in two different spreadsheets that you want to link? The best option there is to use Zapier's Google Sheets workflows to link your sheets. Zapier can watch for new or updated data in your cell, and copy it over to your other spreadsheet to keep things up-to-date across spreadsheets and accounts.

Now, with your data linked, you'll save time without having to copy and paste data–and will be sure to always have the correct data in every sheet.

Question asked by Andrea

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.

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

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.

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

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

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.

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.

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP

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.

📌 Step 2:

➤ Go to Sheet1.

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

➤ Press Enter.

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.

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

Similar Readings

• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• Extract Filtered Data in Excel to Another Sheet (4 Methods)
• How to Import Data from Text File into Excel (3 Methods)
• Convert Excel to Text File with Delimiter (2 Easy Approaches)
• How to Convert Text File to Excel Automatically (3 Suitable Ways)

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.

📌 Step 2:

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

➤ Press Enter.

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.

Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)

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.

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

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.

📌 Step 2:

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

📌 Step 3:

➤ Right-click the mouse button now.

➤ Select the option Properties.

📌 Step 4:

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

📌 Step 5:

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

The VBA window will appear.

📌 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

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

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

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

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

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.

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

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.

Related Articles

• How to Extract Data From Table Based on Multiple Criteria in Excel
• Return Multiple Values in Excel Based on Single Criteria (3 Options)
• Import Data from Excel into Word Automatically Using VBA (2 Ways)
• How to Extract Data from a List Using Excel Formula (5 Methods)
• Extract Data from Cell in Excel (5 Methods)
• How to Extract Data from Excel to Word (4 Ways)