How to Import Data from Another Worksheet in Excel

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.

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.

Read More: Use VBA to Copy Data from Workbook without Opening

Conclusion

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.