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.
Download Practice Workbook
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.
- 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.
- 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.
- How to Split Screen in Excel (3 Ways)
- [Fix:] Excel View Side by Side Not Working
- How to Enable Side-by-Side View with Vertical Alignments in Excel
- Split Sheets into Separate Workbooks in Excel (4 Methods)
- How to Split a Workbook to Separate Excel Files with VBA Code
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.
- 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.
- First, right-click on the sheet and go to View Code.
- At this point, copy and paste the VBA code below.
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.
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.
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.
- How to Separate Sheets in Excel (6 Effective Ways)
- Split Excel Sheet into Multiple Files (3 Quick Methods)
- Excel VBA: Split Sheet into Multiple Sheets Based on Rows
- How to Split Excel Sheet into Multiple Sheets Based on Column Value
- Split Excel Sheet into Multiple Sheets Based on Rows
- How to Split Excel Sheet into Multiple Worksheets (3 Methods)