Do you want to add a sheet to another workbook in Excel? Yes, then you are in the right place! You can add a sheet in Excel in different ways. Microsoft Visual Basic Application is one of them. In this article, we will show how to add a sheet to another workbook using VBA in Excel.
3 Examples with Excel VBA to Add Sheet to Another Workbook
There are lots of ways to add a sheet to another workbook. We are going to show only VBA examples here. Have a look at the below VBA examples.
1. VBA to Add Excel Sheet to Another Workbook
In this section, we will show how to add a new sheet and an existing sheet to another Excel workbook using VBA in Excel.
1.1 Add Sheet to Another Opened Workbook
We will insert a new worksheet into another worksheet here. In this example, the destination workbook is kept open.
📌 Steps:
- First, go to the Sheet Name section at the bottom of each sheet.
- Now, press the right button of the mouse.
- Choose the View Code option from the Context menu.
- Now, the VBA window appears.
- Choose the Module option from the Insert tab.
- This time, the VBA command module appears. We will write the VBA code here.
- Now, we open another Excel file named Destination Workbook which was previously saved in the file explorer.
- Now, go to the VBA module already opened.
- Copy the VBA code and paste it there.
Sub Add_Sheet_to_Another_Workbook()
Dim w_book As Workbook
Set w_book = Workbooks("Destination Workbook.xlsx")
w_book.Worksheets.Add
End Sub- Finally, run that code by pressing the F5 button.
We can see that a new worksheet is added with a default name.
🔎 Code Explanation:
Dim w_book As WorkbookThis declares a variable.
Set w_book = Workbooks("Destination Workbook.xlsx")This sets the value of the variable.
w_book.Worksheets.AddThis adds a worksheet to the worksheet.
Read More: Excel VBA: Add Sheet After Last
1.2 Add Sheet with Data to a New Workbook
In this section, we will copy an existing sheet to a new workbook.
📌 Steps:
- Go to the Data Set_2 sheet. Open the VBA command module.
- Then, put the VBA code here.
Sub Add_Specific_Sheet_to_Another_Workbook_Before_A_Sheet()
Sheets("Data Set_2").Copy Before:=Workbooks _
("Destination Workbook.xlsx").Sheets("Data Set")
End Sub
- Run the code by pressing the F5Â button.
Also, need to mention that the new sheet will be added before the mentioned sheet in the code.
- We can use the below code to add a sheet at the end of the existing sheets.
Sub Add_Specific_Sheet_to_Another_Workbook_At_The_End()
Sheets("Data Set_2").Copy After:=Workbooks("Destination Workbook.xlsx") _
.Sheets(Workbooks("Destination Workbook.xlsx").Sheets.Count)
End Sub
🔎 Code Explanation:
Sheets("Data Set_2").Copy Before:=Workbooks _
("Destination Workbook.xlsx").Sheets("Data Set")This copies Data Set_2 sheet into the Destination Workbook.xlsx before the Data Set sheet.
Sheets("Data Set_2").Copy After:=Workbooks("Destination Workbook.xlsx") _
.Sheets(Workbooks("Destination Workbook.xlsx").Sheets.Count)This copies Data Set_2 sheet into Destination Workbook.xlsx at the end.
Read More: Excel Macro to Create New Sheet and Copy Data
2. VBA to Add an Excel Worksheet in Another Closed Workbook
In this example, we will add a new sheet in another closed workbook with VBA.
📌 Steps:
- Now, the destination file is closed. Go to the VBA command module of the Excel VBA Add Sheet to Another Workbook.xlsm
- Then, put the following VBA code.
Sub Add_Sheet_to_Another_Closed_Workbook()
Dim w_book As Workbook
Set w_book = Workbooks.Open _
("D:\Softeko_Alok\25-0186_5580\Destination Workbook.xlsx")
w_book.Worksheets.Add
End Sub
- Now, press the F5 button to run the code.
We can see another new sheet added to the Destination Workbook.xlsx file.
🔎 Code Explanation:
Dim w_book As WorkbookDeclare a variable with this.
Set w_book = Workbooks.Open _
("D:\Softeko_Alok\25-0186_5580\Destination Workbook.xlsx")Set the value of the variable with this macro.
w_book.Worksheets.AddAdd a new worksheet to the workbook.
3. VBA to Create a New Excel File and Add Sheet with Data from a Previous Workbook
Here, we will explain how to copy an existing sheet from an existing workbook to a new workbook.
📌 Steps:
- Go to the VBA module.
- Write the following VBA code here.
Sub Add_Sheet_from_One_Workbook_to_Another_Workbook()
Dim w_book As Workbook
Set w_book = Workbooks("Excel VBA Add Sheet to Another Workbook.xlsm")
w_book.Sheets("Data Set").Copy
End Sub
- Again, press the F5 button to run the code.
Here, we can see that a workbook named Book6.xlsx is created with the Data Set sheet.
🔎 Code Explanation:
Dim w_book As WorkbookDeclare a variable.
Set w_book = Workbooks("Excel VBA Add Sheet to Another Workbook.xlsm")Set the value of the variable.
w_book.Sheets("Data Set").CopyThis will add the Data Set sheet to a new workbook.
How to Add Sheet Within Same Workbook with VBA
So far, we have discussed how to add sheet to other workbook using Excel VBA. But what if you want to add sheet to the existing Excel file? So in this section, we will show how to add a sheet within the same workbook.
📌 Steps:
- Go to the command module.
- Put the following VBA code.
Sub Add_Sheet_Within_Same_Workbook_1()
Sheets("Data Set_2").Copy Before:=Sheets("Sheet3")
End Sub
- Run the code by pressing the F5Â button.
We can see the existing sheet has been added to the existing workbook.
🔎 Code Explanation:
Add_Sheet_Within_Same_Workbook_1()
Sheets("Data Set_2").Copy Before:=Sheets("Sheet3")This code copies the Sheet2 sheet and pastes it before the Sheet3 sheet within the same workbook.
Read More: How to Add Sheet with Name in Excel VBA
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 4 examples to add sheets to another workbook using VBA code. We also showed how to add a sheet within the same workbook. I hope this will satisfy your needs.
