How To Transfer Data From One Excel Worksheet To Another Automatically -

Transferring data from one sheet to another automatically can be very useful for a range of reasons however there are a wide number of ways this can be done. In this article will cover several different ways to transfer data which includes the following;

Transfer data into another sheet by referencing cells directly
transferring data automatically in a template using a vlookup

creating a macro to transfer data within a workbook from one sheet to another
creating a macro to transfer data from one workbook to another.

Some of the methods discussed in this article will require the use of the VBA; however, we have designed the code to make it easy for somebody that has no experience with VBA to implement the code and modify it to suit their specific needs.

Transfer Data Into Another Sheet By Referencing Cells Directly

The simplest method to transfer data from one sheet to another, other than pasting data from one sheet to another, is to reference the cells directly in formulas. To do this, place an equal sign into the cell you want the data to be transferred to and then select the sheet and cell where the data is coming from to create a formula that references data from another sheet.

Transferring Data Automatically In A Template Using A Vlookup

Transferring data from one sheet to another using a vlookup is a very useful method for creating templates that will allow data to be imported automatically based on user input. This method is simple and can be done by anyone with a reasonable understanding of formulas within Excel.

To show how this method might be used a set of data has been created which is shown below.

Step 1: To allow our user to select the data accurately it is necessary to create a drop-down list of the product names listed in the spreadsheet. This can be done by highlighting the product names and then creating a name for this range in the area located above column A.

Step 2: To create a drop-down list within a cell on another sheet start by selecting the cell and then select data and data validation.

Step 3: Select list and in the source field place an equals sign and then the title of the named range. This will create a dropdown list in the cell.

Step 4: Add a vlookup formula to the cell. The vlookup formula should contain an IF statement to avoid errors when the cell is blank. An example of the formula is provided below;

=IF(A1=””,””,VLOOKUP(A1,Sheet3!A:B,2,FALSE))

Explanation

The structure of the IF statement is IF(logic_test, True, False). The logic test in the formula is A1=”” which means is the cell blank. If the cell is blank the true statement is “” which means leave the cell blank otherwise complete a VLOOKUP.

VLOOKUP(A1,Sheet3!A:B,2,FALSE)

A1 = The lookup value

Sheet3!A:B = The data array where the data is located

2 = The column reference, selects which column from the array is displayed.

FALSE = An exact match.

Creating A Macro To Transfer Data Within A Workbook From One Sheet To Another

A recorded macro can easily be created to transfer data from one sheet to another. To do this, you need to use the developer tab on Excel which is not shown automatically. To make the developer TAB visible click file in the top left-hand corner of the screen and then select options in the bottom left-hand side of the screen. To make the tab visible select customize ribbon and check the developer TAB as shown in the screenshot below.

To record a macro that transfers the sheet from one tab to another complete the following steps;

Step 1: Select the developer TAB, and select record macro.

Step 2: Name the macro and assign a shortcut key (optional)

Step 3: Goto the sheet where the data is that is to be copied, highlight the data to be transferred, and copy the data.

Step 4: Select the sheet and cell location where the data is to be copied to and paste the data.

Step 5: Select the developer tab and select Stop Recording

The macro can be triggered automatically by altering the code generated in the VBA editor for information on how to do this see the next section of the article.

Creating A Macro To Transfer Data From One Workbook To Another

Transferring data automatically from one workbook to another is very useful as it allows you to store centralized data and update multiple files simultaneously using this method. The method requires the use of VBA code and can be triggered manually or automatically upon a given event.

The example we have provided below is triggered upon opening a file. This is triggered by the name of the macro which is Auto_Open. If you change the name of the macro to Auto_Close it will trigger the macro to run upon closing of the file. Alternatively, the macro can be renamed to anything else, and will you need to trigger it manually.

To customize the code to suit your specific file you will need to modify the destination range, the file path where the data is coming from, and the filename. All of the positions where the code needs to be altered are highlighted in bold below.

To install the code into your file you need to access the Visual Basic editor by hitting ALT + F11. It will then be necessary to create a new module in which to place the code. This can be done by selecting insert at the top of the screen and then module. This will create a new module with an area for you to copy the code below into. An example of what your screen should look like is shown below.

Sub Auto_Open()

Dim srceRng As Range

Dim destRng As Range

'sets destination ranges

Set destRng = Worksheets("sheet1").Range("A1")

‘Opens file where the data is coming from

Workbooks.Open Filename:="C:\documents\Upload_Test.xlsx", UpdateLinks:=False 'open file

'sets where files come from

Set srceRng = Workbooks("Upload_Test.xlsx").Sheets("sheet1").Range("A:Z") 

srceRng.Copy destRng

Workbooks("Upload_Test.xlsx").Close False 'closes files

End Sub