How to Open Multiple Excel Files in One Workbook (4 Easy Ways)

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:

Table of Contents hide

Download Practice Workbook

4 Ways to Open Multiple Excel Files in One Workbook

Method 1: Open Multiple Excel Files in One Workbook by Copying

Method 2: Open Multiple Files Using Move or Copy Option

Method 3: Link to Open Multiple Excel Files

Method 4: Using VBA to Open Multiple Files in One Workbook

Practice Section

Conclusion

Download Practice Workbook

Multiple Files in One Workbook (File 1).xlsm

4 Ways to Open Multiple Excel Files in One Workbook

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 here 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.

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 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.

Similar Readings

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 inerting 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

At 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.

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.