How do I automatically transfer data from one sheet to another?

With more than 30 million users worldwide, Excel is the most popular spreadsheet software in the world. Thanks to its variety of analytical and collaborative features, Excel is one of the best tools to store, manage and share large amounts of data both individually and with teams.

Although Excel offers a lot of great features, when it comes to data management, you still have to carry out a lot of these processes manually. This includes transferring your data from one worksheet to another. There are a few ways to do this within Excel, such as copying and pasting or using a simple formula.

However, manually transferring data across different sheets can not only be time-consuming but can also create more room for error. Using an automated alternative that you can still use within Excel may be the best solution to save time and ensure data accuracy.

Let’s explore in more detail how to transfer data from one worksheet to another in Excel, and how the Sheetgo add-in is a great alternative to automate this entire process.

This article will teach you how to transfer data from one spreadsheet to another in Microsoft Excel if your copy and paste function is not working. The two methods presented will use a quick formula that you can apply to data present in the same Microsoft Excel file. This article will show you how to transfer data from one excel worksheet to another automatically.

How to transfer data from one spreadsheet to another? For each example, consider that we have two sheets: Sheet 1 and Sheet 2, and we would like to transfer from cell A1 of Sheet 1 to cell B1 of Sheet 2. Using the + symbol in Excel ... 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.

How do I automatically transfer data from one sheet to another?


In the following picture, the Dataset worksheet is representing the specifications of a number of smartphone models.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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.

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

  • After selecting the Paste Link, you will notice that the Price column is now complete with the extracted data from the first sheet (Dataset).

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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.

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

How do I automatically transfer data from one sheet to another?

  • On the other hand, we have only the Brand and Price columns in the destination dataset.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?


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.

How do I automatically transfer data from one sheet to another?

  • Go to the Dataset worksheet.
  • Select the range of cells (D5:D10) containing the prices of all smartphone models.
  • Press Enter.

How do I automatically transfer data from one sheet to another?

  • In the Worksheet Reference worksheet, you’ll find an array of prices in column D ranging from D5 to D10.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

  • In the Dataset worksheet, select the range of cells (D5:D10) containing the prices of all devices.
  • Press Enter.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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.

How do I automatically transfer data from one sheet to another?

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.

How do I automatically transfer data from one sheet to another?

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.

How do I automatically transfer data from one sheet to another?

  • Now draw the rectangle according to your preferred size using the arrow shown in the image in the picture.

How do I automatically transfer data from one sheet to another?

  • And you’ll see the command button as shown in the following screenshot.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

  • In the Caption box, assign the button name, let’s say it’s ‘Transfer to VBA_2 Sheet.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?


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.

How do I automatically transfer data from one sheet to another?

  • 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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

  • Switch back to the VBA_2 worksheet and then you will notice that the new information is now right below the existing dataset.

How do I automatically transfer data from one sheet to another?

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

How do I automatically transfer data from one sheet to another?

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


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

How do I automatically transfer data from one Excel sheet to another?

Here's how:.
Select all the data in the worksheet. Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar..
Copy all the data on the sheet by pressing CTRL+C..
Click the plus sign to add a new blank worksheet..
Click the first cell in the new sheet and press CTRL+V to paste the data..
Use the IMPORTRANGE function.
In Sheets, open a spreadsheet..
In an empty cell, enter =IMPORTRANGE..
In parenthesis, add the following specifications in quotation marks and separated by a comma*: The URL of the spreadsheet in Sheets. ... .
Press Enter..
Click Allow access to connect the 2 spreadsheets..

How do I autofill the same cell from different sheets to a sheet in Excel?

Click the tab for the first worksheet that you want to reference. Hold down the Shift key then click the tab for the last worksheet that you want to reference. Select the cell or range of cells that you want to reference. Complete the formula, and then press Enter.