Sometimes, we may feel the need to open multiple Excel files in one workbook for better presentation and ease of moving from one file to another. In this article, we use several methods on how to open multiple Excel files in one workbook. For your better understanding, we have attached the sample workbook here.
Main File:
File 1:
File 2:
Open Multiple Excel Files in One Workbook: 4 Ways
We will use File linking, VBA code, and the Copy method to open multiple Excel files in one workbook.
Method 1: Open Multiple Excel Files in One Workbook by Copying
Suppose we are working on an Excel workbook File 1 and want to add two other files, “Adding Two Columns” and “C.P-S.P” from workbook File 2 and File 3 for ease of our work. The easiest way we can do this is by copying the files here.
Steps:
- First, add a sheet in the workbook File 1.
- After that, go to Adding Two Columns sheet in File 2 and press CTRL+A, then press CTRL+C.
- Now, go to File 1 and the sheet we recently added and press CTRL+V to paste.
- So, do the same thing, copy and paste for the sheet C.P-S.P, and our workbook will look like the following image.
Read More: How to Open Two Excel Files Separately
Method 2: Open Multiple Files Using Move or Copy Option
Another easy way is to Move or Copy the sheet to the same or another workbook. For this method, we have to keep all the workbooks open.
Steps:
- First, right-click the mouse button on the sheet Accrued Manually in File 1 and click Move or Copy.
- As a result, a dialogue box will pop up, and we will select our location as File 1 in the To book section, click Move to end, and check Create a copy before clicking OK.
- As a result, the file will be automatically copied to the desired location.
- Now, do the same for sheet C.P-S.P and our final result will be like the following image.
Read More: How to Open Excel Files in Separate Windows by Default
Method 3: Link to Open Multiple Excel Files
We can also use file links for this purpose. What we have to do is just link the files so that we can open them with just a click.
Steps:
- At first, click on cell C12 and press CTRL+K and a dialogue box will pop up. We will select the File, Folder accordingly but keep the setting Existing File or Web page and Current Folder as it is.
- After that, click OK, and we will get the link as follows.
- So, do the same for inserting File 3.
As a consequence, if you click the links, the files will be opened from this workbook.
Method 4: Using VBA to Open Multiple Files in One Workbook
In our last method, we will see a VBA code to open multiple Excel files in one workbook.
Steps:
- First, right-click on the sheet and go to View Code.
- At this point, copy and paste the VBA code below.
VBA code:
Option Explicit
Sub OpenMultiSheetsinOne()
Dim Root As String
Dim fname As String
Dim Sht As Worksheet
Root = "C:\Users\Mahbub\Desktop\Open Multi Files\"
fname = Dir(Root & "*.xlsx")
Do While fname <> ""
Workbooks.Open Filename:=Root & fname, ReadOnly:=True
For Each Sht In ActiveWorkbook.Sheets
Sht.Copy After:=ThisWorkbook.Sheets(1)
Next Sht
Workbooks(fname).Close
fname = Dir()
Loop
End Sub
- Just keep in mind that, you have to give the location of your file in the second line of the code path=””
- Just go to the location folder and click the drop-down arrow.
- Finally, press the F5 or play button to run the code.
That’s it.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Download Practice Workbook
Conclusion
That’s all for the article. These are 4 different methods on how to open multiple Excel files in one workbook. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
