In this article, we will learn to insert a sheet from another file in Excel. Excel users often need to insert files from another file. Sometimes, we insert a single sheet, and sometimes multiple sheets. Today, we will demonstrate 3 easy ways. Using these methods, you can easily insert sheets from another workbook according to your needs. So, without further delay, let’s start the discussion.
To explain the methods, we will use 2 Excel files. The name of the first workbook is Insert Sheet from Another File, and the second one is List 1. We have different sheets in the first workbook. These sheets contain different information.
In the second workbook, we have a single sheet named “List 1”. We will insert sheets into the second file named List 1 from the first Excel file.
1. Using Sheet Tab to Insert Sheet in Excel from Another File
In Excel, we can use the Sheet Tab to insert a sheet from another file. Here, we will show the way to insert both single and multiple sheets. Let’s pay attention to the sections below to learn more.
1.1 For Single Sheet
To insert a single sheet, you need to follow the steps below.
STEPS:
- First of all, open both Excel files.
- Then, go to the Sheet Tab of the file from where you want to send the sheet.
- In our case, we want to insert the sheet named ‘Sheet Tab – Single’ into another file named ‘List 1’.
- To do so, right–click on the sheet name to open the Context Menu.
- Select Move or Copy from there. It will open the Move or Copy dialog box.
- In the Move or Copy dialog box, select the file in the ‘To book’ field where you want to insert the sheet.
- Also, select ‘(move to end)’ in the ‘Before sheet’ box.
- Check ‘Create a copy’ to copy the sheet.
- If you want to move the sheet completely, then uncheck it.
- Click OK to move forward.
- As a result, you will see the desired sheet in the ‘List 1’ file.
Read More: How to Create Multiple Worksheets from a List of Cell Values
1.2 For Multiple Sheets
We can use the same method to insert multiple sheets from another file. Let’s observe the steps below to see how we can do it.
STEPS:
- In the beginning, open both files.
- Then, hold the Ctrl key and select the sheets you want to insert another file.
- After that, right–click on any selected sheet name to open the Context Menu.
- Select Move or Copy from there. It will open the Move or Copy dialog box.
- In the following step, select the file where you want to insert the sheets in the ‘To book’ box.
- Also, select ‘(move to end)’ in the ‘Before sheet’ box.
- Check ‘Create a copy’ to copy the sheet.
- If you want to move the sheets completely, then uncheck it.
- Click OK to move forward.
- Finally, you will see the multiple sheets in the file named ‘List 1’.
Read More: How to Create Multiple Sheets in Excel with Different Names
2. Inserting Sheet by Dragging from Another File in Excel
You can also insert a sheet by dragging it from another file in Excel. In the previous method, we could Move or Copy the sheet. But if you drag a sheet from one file to another file, then it will move to the new file and will not exist in the old one. Here, we will drag the sheet named ‘Drag Sheets’ and insert it into the file named ‘List 1’. So, follow the steps below to learn the method completely.
STEPS:
- Firstly, open both files and view them side by side.
- Secondly, click on the desired sheet and drag it to the file where you want to insert it.
- At the time of dragging, the cursor will change into a rectangular sheet symbol.
- In the following step, place the cursor where you want to place the sheet.
- Finally, you will see the desired sheet in the ‘List 1’ file.
3. Applying Excel VBA to Add a Sheet from Another File
Excel VBA helps users to perform different tasks easily with a few lines of code. VBA stands for Visual Basic Applications. We can use VBA to insert a sheet in an active workbook and also in a closed workbook. We will show both cases in the following sections.
3.1 Insert Sheet in Active Workbook
You can insert a sheet in an active workbook from another workbook. You need to open both files for this purpose. Here, we will use insert a sheet named ‘VBA Active’ in another workbook named ‘List 1’ from the ‘Insert Sheet from Another File’ workbook. So, let’s pay attention to the steps to learn the whole technique.
STEPS:
- In the first place, open both sheets.
- Then, go to the workbook from which you want to insert the sheet and click on the Developer tab.
- Now, select the Visual Basic option. It will open the Visual Basic window.
- Alternatively, you can press Alt + F11 together to open the Visual Basic window.
- Then, select Insert. A drop-down menu will appear.
- Select Module from there. It will open the Module window.
- In the Module window, type the code below:
Sub Insert_to_Another_File()
Sheets("VBA Active").Copy After:=Workbooks("List 1.xlsx").Sheets(Workbooks("List 1.xlsx").Sheets.Count)
End Sub
This code will insert a sheet named ‘VBA Active’ to a file named ‘List 1’. The sheet will be added after the last existing sheet. You need to change these things inside the code according to your sheet and file names.
- Press Ctrl + S to save the code.
- In the following step, press the F5 key to run the code.
- Otherwise, close the Visual Basic window and navigate to the Developer tab.
- Then, select Macros to open the Macro window.
- In the Macro window, select the code and Run it.
- As a result, you will find the ‘VBA Active’ sheet in the ‘List 1’ file.
3.2 Add Sheet in Closed Workbook
Like the previous method, you can also use VBA to add a sheet in a closed workbook in Excel. You need to repeat the same steps as the previous section and change the VBA code. Let’s observe the steps below to see how we can do it.
STEPS:
- Firstly, open the file from where you want to add the sheet.
- In the second step, press Alt + F11 to open the Visual Basic window.
- Then, click on Insert and select Module from the drop-down menu.
- Now, type the code below in the Module window:
Sub Insert_to_Closed_Workbook()
Dim closedbook As Workbook
Application.ScreenUpdating = False
Set closedbook = Workbooks.Open("D:\exceldemy\New folder (103)\List 1.xlsx")
Workbooks("Insert Sheet from Another File.xlsm"). Sheets("VBA Closed").Copy Before:=closedbook.Sheets(1)
closedbook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
This code will open the file named List 1. The location of the file on my computer is “D:\exceldemy\New folder (103)\List 1.xlsx“. You must change this part and add the location of your closed file. We are applying this code in the file named “Insert Sheet from Another File” and we will insert the sheet named “VBA Closed”. You have to change this information according to your needs.
- At this moment, press Ctrl + S to save the code and press the F5 key to Run it.
- Lastly, open the closed file. You will find the desired sheet in the first sheet there.
Read More: How to Create New Sheets for Each Row in Excel
Download Practice Workbook
You can download the practice book from here.
Conclusion
In this article, we have 3 easy methods to Insert a Sheet in Excel from Another File. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
