Excel VBA to Add Sheet to Another Workbook (3 Handy Examples)

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.


Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


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.

View Code Command

  • Now, the VBA window appears.
  • Choose the Module option from the InsertΒ tab.

Open a New VBA Module

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

VBA to Add Excel Sheet to Another Workbook

We can see that a new worksheet is added with a default name.

πŸ”Ž Code Explanation:

Dim w_book As Workbook

This declares a variable.

Set w_book = Workbooks("Destination Workbook.xlsx")

This sets the value of the variable.

w_book.Worksheets.Add

This adds a worksheet to the worksheet.

Read More: Excel VBA: Add Sheet After Last (3 Ideal Examples)


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

VBA to Add Excel Sheet with Data to Another Workbook

  • Run code by pressing the F5Β button.

Also, need to mention that the new sheet will add 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

VBA to Add Excel Sheet with Data to End of Another Workbook

πŸ”Ž 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: How to Add Sheet with Name from Cell Using Excel VBA


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

VBA to Add an Excel Worksheet in Another Closed Workbook

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

Declare 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.Add

Add a new worksheet to the workbook.

Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)


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

VBA to Create a New Excel File and Add Sheet with Data from a Previous Workbook

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

Declare 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").Copy

This will add the Data Set sheet to a new workbook.

Read More: Macro to Create New Sheet and Copy Data in Excel (4 Examples)


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

Add Sheet Within Same Workbook with VBA

  • 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 copy of the Sheet2 sheet and paste it before the Sheet3 sheet within the same workbook.

Read More: How to Add Sheet with Name in Excel VBA (6 Easy Ways)


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. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo