How to Insert Sheet from Another File in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

3 Easy Ways to Insert Sheet from Another File in Excel

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.

3 Easy Ways to Insert Sheet from Another File in Excel


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, rightclick on the sheet name to open the Context Menu.
  • Select Move or Copy from there. It will open the Move or Copy dialog box.

Use Sheet Tab to Insert Sheet in Excel from Another File

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

Use Sheet Tab to Insert Sheet in Excel from Another File

  • As a result, you will see the desired sheet in the ‘List 1’ file.

Use Sheet Tab to Insert Sheet in Excel from Another 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.

Use Sheet Tab to Insert Sheet in Excel from Another File

  • After that, rightclick 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.

Use Sheet Tab to Insert Sheet in Excel from Another File

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

Use Sheet Tab to Insert Sheet in Excel from Another File

  • Finally, you will see the multiple sheets in the file named ‘List 1’.

Use Sheet Tab to Insert Sheet in Excel from Another File

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.

Insert Sheet by Dragging from Another File in Excel

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

Insert Sheet by Dragging from Another File in Excel

  • In the following step, place the cursor where you want to place the sheet.

Insert Sheet by Dragging from Another File in Excel

  • Finally, you will see the desired sheet in the ‘List 1’ file.

Insert Sheet by Dragging from Another File in Excel


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.

Apply Excel VBA to Add Sheet from Another File

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


Related Articles


<< Go Back to  Insert Sheet | Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo