How to Copy Data from Another Workbook without Opening with Excel VBA: 3 Different Ways

We will use a sample Workbook named Product_Details and copy the data range (B4:E10) to another Workbook.

Method 1 – Copy a Sheet Data from Another Workbook without Opening with Excel VBA

STEPS:

• Go to the Developer tab.
• Click on Visual Basic to open the Visual Basic Editor. (Note: You can also use keyboard shortcut  Alt + F11, or, right-click on the sheet, then select View Code)

• Copy-paste the VBA code below to the editor.

VBA Code:

Sub Copy_Data_from_Another_Workbook()
Dim wb As Workbook
Dim newwb As Workbook
Dim rn1 As Range
Dim rn2 As Range
Set wb = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Application.Workbooks.Open .SelectedItems(1)
        Set newwb = Application.ActiveWorkbook
        Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
        wb.Activate
        Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
        rn1.Copy rn2
        rn2.CurrentRegion.EntireColumn.AutoFit
        newwb.Close False
    End If
End With
End Sub
End With
End Sub

• Click the Run Sub button, or press the keyboard shortcut F5, to run the code.

• The File Open window will pop up.
• Select the workbook you want to collect data from.
• Click on the OK button.

• Select the data from the source file by dragging over the desired range (example, B5:E10) and click OK.

• Select the destination range where you want to put the data.
• Click OK.

• The source file will be closed and the data will be copied to the destination file.

Read More: Excel VBA: Copy Range to Another Workbook

Method 2 – VBA to Copy a Data Range from Another Workbook without Opening in Excel

STEPS:

• Go to the Developer tab.
• Open the Visual Basic Editor by clicking on Visual Basic (or by pressing Alt + F11, or right-clicking on the sheet and selecting View Code).

• Copy-paste the VBA code below.

VBA Code:

Sub CollectData()
Dim rgTarget As Range
Set rgTarget = ActiveSheet.Range("B2:E10") 'where to put the copied data.
rgTarget.FormulaArray = "='D:\[Product_Details.xlsx]Sheet1'!$B$4:$E$10"
rgTarget.Formula = rgTarget.Value
End Sub

• Click Run Sub or press the keyboard shortcut F5 to run the code.

NOTE: You need to change the range in the code as per your source data.

• The data will be copied into the active workbook.

Read More: Macro to Copy and Paste from One Worksheet to Another

Method 3 – Excel VBA to Copy Data from Another Workbook without Opening by Using Command Button

STEPS:

• Go to the Developer tab.
• Click on the Insert drop-down menu.
• Click on the Command Button.

• Put the source file sheet name (example, Product) on cell A1. Set the Command Button on the right side of the source file sheet name. Create the table required for the data.

• Go to the Developer.
• Click on Visual Basic to launch the Visual Basic Editor.

• Copy-paste the VBA code below.

VBA Code:

Private Sub CommandButton1_Click()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Set currentworkbook = ThisWorkbook
Set sourceworkbook = Workbooks.Open("D:\Products_Details.xlsx")
sourceworkbook.Worksheets("Product").Range("B5:E10").Copy
currentworkbook.Activate
currentworkbook.Worksheets("Sheet3").Activate
currentworkbook.Worksheets("Sheet3").Cells(4, 1).Select
ActiveSheet.Paste
sourceworkbook.Close
Set sourceworkbook = Nothing
Set currentworkbook = Nothing
ThisWorkbook.Activate
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A2").Select
End Sub

• Press Ctrl + S to save the code.

NOTE: You need to change the file path and the data range in the code.

• Click on the CommandButton1 to copy the data from another workbook without opening it.

Read More: Macro to Copy Data from One Workbook to Another Based on Criteria

Download Practice Workbook

You can download the workbook and practice with them.

Related Articles

• How to Use Excel VBA to Copy Range to Another Excel Sheet
• Macro to Copy Specific Columns from One Worksheet to Another in Excel
• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• How to Open Another Workbook and Copy Data with Excel VBA
• Excel VBA to Loop Through Files in Folder and Copy Data
• How to Open All Excel Files in a Folder and Copy Data Using VBA