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

Get FREE Advanced Excel Exercises with Solutions!

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:

how to open multiple Excel files in one workbook

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.

how to open multiple Excel files in one workbook by Copy

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

how to open multiple Excel files in one workbook by Copy

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

how to open multiple Excel files in one workbook by 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.

how to open multiple Excel files in one workbook by Move or copy

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

how to open multiple Excel files in one workbook by link

  • After that, click OK, and we will get the link as follows.

how to open multiple Excel files in one workbook Links

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

how to open multiple Excel files in one workbook with VBA

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

how to open multiple Excel files in one workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo