This post will guide you on how to import data from another worksheet using the Connections function in Excel. You can easily import data from other worksheets in the same workbook or from external workbooks through Connections or VBA code in Excel.
Import Data from Another Worksheet with Connections Function
The Connections function in Excel allows you to import data from another worksheet, which can be useful when you need to consolidate data from multiple sources or keep your data organized in separate sheets.
Here’s how to use the Connections function to import data from another worksheet:
Step1: Open the workbook that contains the data you want to import.
Step2: Click on the worksheet where you want to import the data.
Step3: Click on the “Data” tab in the Excel ribbon. Click on “Existing Connections” in the “Get External Data” section of the ribbon.
Step4: In the “ Existing Connections ” dialog box, click on “Browse for More…“.
Step5: locate and select the Excel workbook that contains the data you want to import. Click on “Open” button in the “Select Data Source” dialog box.
Step6: Choose the worksheet that contains the data you want to import, and then click “OK“.
Step7: Choose how you want to view and organize the data in the imported worksheet. For example, choose Table option, and select one destination Cell to put the imported data.
Step8: Click “OK” to create the connection and import the data.
When you use the Connections function to import data, Excel creates a link between the source worksheet and the destination worksheet. If you delete or move the source worksheet, the link will be broken and you won’t be able to access the data in the destination worksheet.
Import Data from Another Worksheet with VBA Code
You can also import data from another worksheet using VBA code in Excel, you can use the Application.FileDialog function to prompt the user to select the source file, and then use the Workbooks.Open and Range.Copy methods to copy the data to the destination worksheet. Just do the following steps:
Step1: Open a new or existing Excel workbook that you want to import data in.
Step2: Press “Alt + F11” shortcut to open the Microsoft VBA Editor.
Step3: Insert a new module by clicking “Insert” > “Module“.
Step4: Copy and paste the below code into the module window.
Sub ImportDataFromWorksheet_excelgeek() Dim MyFile As String Dim MyPath As String Dim MyWorkbook As Workbook Dim MySourceRange As Range Dim MyDestination As Range Set currentWorkbook = ActiveWorkbook ' Prompt the user to select the source file With Application.FileDialog(msoFileDialogFilePicker) .Title = "Select the source file" .Filters.Clear .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1 .Show If .SelectedItems.Count > 0 Then MyFile = .SelectedItems(1) MyPath = Left(MyFile, InStrRev(MyFile, "\")) Else MsgBox "No file selected.", vbExclamation, "Error" Exit Sub End If End With ' Open the source workbook Set MyWorkbook = Workbooks.Open(MyFile) Set MySourceRange = Application.InputBox(prompt:="Select source range from source worksheet", Title:="Select Source Range", Default:="A1", Type:=8) currentWorkbook.Activate Set MyDestination = Application.InputBox(prompt:="Select one destination cell", Title:="Select Destination to put data", Default:="A1", Type:=8) MySourceRange.Copy MyDestination MyWorkbook.Close SaveChanges:=False End Sub
Step5: In your current worksheet, press “Alt +F8” shortcut to open Macro window. Then select the Macro name “ImportDataFromWorksheet_excelgeek”, click Run button.
Step6: Select the source workbook that contains worksheets you want to import. Click Open button.
Step7: choose one worksheet and then select source range from the current worksheet. For example: test3!$A$1:$G$11, click OK button.
Step8: select one destination cell in your worksheet. Click OK button.
Step9: You would see that the selected data has been imported into your worksheet.
There are a few different methods to import data from another worksheet in Excel. One option is to use the Connections function, which allows you to link to data in another worksheet or workbook. Another option is to use VBA code.