In Microsoft Excel, VBA Macros can easily solve a wide range of problems. If we want to copy data from another workbook without opening the workbook, we can easily do it by using Excel VBA. In this article, you will learn Excel VBA to copy data from another workbook without opening it.
Download Practice Workbook
You can download the workbook and practice with them.
3 Different Ways to Copy Data from Another Workbook without Opening with Excel VBA
Sometimes, we need data from some previous workbook. If we are in a hurry and need the data immediately without opening the workbook, we can use the Excel VBA. With Excel VBA, we can quickly copy the data from other workbooks, for this, we just need to know the location of that particular workbook.
To copy the data we are going to use the workbook name Product_Details. And we want to copy the data range (B4:E10). The dataset we want to copy contains some products, their selling price, cost of goods, and gross profit margins. Let’s look at different criteria to copy data from another workbook.
1. Copy a Sheet Data from Another Workbook without Opening with Excel VBA
We can copy data from a sheet by following the VBA code below. For this, we need to go through the below steps.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- After that, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, right-click on the sheet, then select View Code.
- Now, write down the VBA code below.
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
- Finally, run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.
NOTE: You don’t need to modify the code. Just copy and paste the code.
- By running the code File Open window will appear from your computer.
- After that, click on the workbook you want to collect data.
- Then, click on the OK button.
- Now, select the data from the source file by dragging over the range B5:E10 and then click OK.
- After selecting the data range. Now select the destination range where you want to put the data.
- And, click OK.
- In the end, this will close the source file and the data will copy on the destination file.
Read More: Excel VBA: Copy Range to Another Workbook
Similar Readings
- How to Paste From Clipboard to Excel Using VBA
- Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- Excel VBA to Copy Rows to Another Worksheet Based on Criteria
- How to Use VBA to Paste Values Only with No Formatting in Excel
2. VBA to Copy a Data Range from Another Workbook without Opening in Excel
By using the VBA code below, we can copy data from a data range. We must follow the steps below to accomplish this.
STEPS:
- To begin, navigate to the Developer tab on the ribbon.
- Second, open the Visual Basic Editor by clicking on Visual Basic or by pressing Alt + F11.
- Or, simply right-click on the sheet and select View Code to open the Visual Basic Editor.
- After that, write down the VBA code there.
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
- Here, run the code using Run Sub or press the keyboard shortcut F5 to run the code.
NOTE: You don’t need to modify the code, all you need to do is just change the range as per your source data.
- And finally, the data is now copied from another workbook into the active workbook.
Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)
3. Excel VBA to Copy Data from Another Workbook without Opening by Using Command Button
We can copy data from another workbook by using the command button on the VBA code. To accomplish this, we must follow the steps outlined below.
STEPS:
- Firstly, to put a Command Button, go to the Developer tab.
- Secondly, click on the Insert drop-down menu.
- Thirdly, click on the Command Button.
- We put the Product on cell A1, as it’s our source file sheet name. And we set the Command Button, on the right side of the source file sheet name. We created the table now, we just need the data which is in another workbook.
- By the same token, navigate to the Developer tab on the ribbon.
- Next, click on Visual Basic or press Alt + F11 to launch the Visual Basic Editor.
- You can also open the Visual Basic Editor by right-clicking on the sheet and selecting View Code.
- Now, write the VBA code down.
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
- Then, save the code by pressing Ctrl + S.
NOTE: You can copy the code, you just need to change the file path and the data range.
- And, finally, if you click on the CommandButton1 this will copy the data from another workbook without opening it.
Read More: Macro to Copy Data from One Workbook to Another Based on Criteria
Conclusion
The above criteria are guidelines to copy data from another workbook without opening it with Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
Related Articles
- How to Copy Rows in Excel (4 Easy Ways)
- Copy and Paste Thousands of Rows in Excel (3 Ways)‌
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- Copy the Same Value in Multiple Cells in Excel (4 Methods)
- How to Copy and Paste in Excel Without Changing the Format
- Excel Formula to Copy Cell value to Another Cell
- How to Update One Excel Worksheet from Another Sheet Automatically
This has been really helpful, thank you.
I used version: 1. Copy a Sheet Data from Another Workbook without Opening with Excel VBA, and its worked brilliantly, I do have one questions which is can the code be altered so that it pastes the values rather than the formatting of the cell?
For example, when I use this code it pastes the drop down data validation from the workbook to the destination workbook, but I want to keep the validation as is on the destination workbook.
Hope this makes sense
Hi PETE,
Thanks for your comment. I am replying on behalf of Exceldemy. To paste the values, you can try the code below:
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.PasteSpecial xlPasteValues
newwb.Close False
End If
End With
End Sub
Here, we have used the code of Method 1. We changed the highlighted lines to copy and paste only the values.
I hope this will help you solve your problem. Please let us know if you have any other queries.
Thanks!
just to the point
thanks
Thanks for your appreciation.
Regards
ExcelDemy