Looking for ways to know how to create a new workbook and name it using VBA in Excel? Sometimes, we want to create a new workbook with a name in Excel. We can do it by using VBA. Here, you will find 6 ways to create a new workbook and name it using VBA in Excel.
New Workbook and Name It Using VBA in Excel: 6 Ways
Here, you will find 6 different step-by-step ways to create a new workbook and name it according to your preference using VBA in Excel.
1. Using Add Method to Create New Workbook and Name It
Here, we have a workbook with several Worksheets. In the first method, we will create a new workbook and name it using the Add Method in Excel VBA. This method is used to add members in Excel VBA.
Follow the steps given below to do it on your own.
Steps:
- Firstly, go to the Developer tab >> select Visual Basic.
- Now, Microsoft Visual Basic for Application box will open.
- After that, click on Insert >> select Module.
- Then, write the following code in your Module.
Sub Using_Add_Method_Create_New_Workbook_With_Name()
   Workbooks.Add
   ActiveWorkbook.SaveAs Filename:="F:\Using Add Method.xlsx"
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Using_Add_Method_Create_New_Workbook_With_Name().
- Then, we used the Add Method to create a new workbook.
- After that, we saved the workbook using the SaveAs Method where I declared the Folder name and named it Using Add Method.
- After that, go to the Developer tab >> click on Macros.
- Now, the Macros box will appear.
- Then, select Using_Add_Method_Create_New_Workbook_with_Name.
- After that, click on Run.
- Finally, you will be able to create a new workbook with a name as Using Add Method.
2. Applying Set Object to Create New Workbook and Name It
Now, we will show you how to create and name a new workbook by applying the Set object in Excel VBA. This object is used to assign any value to a variable. Go through the steps given below to do it on your own.
Steps:
- In the beginning, go to the Developer tab >> select Visual Basic.
- Then, insert a module going through the step shown in Method 1.
- After that, write the following code in your Module.
Sub Applying_Set_Object_Create_New_Workbook_With_Name()
   Dim New_Workbook As Workbook
   Set New_Workbook = Workbooks.Add
   New_Workbook.SaveAs Filename:="F:\Applying Set Object.xlsx"
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Applying_Set_Object_Create_New_Workbook_With_Name().
- Next, we declared New_Workbook as Workbook.
- Then, we created a new workbook using the Add Method and set it as New_Workbook.
- After that, we saved the workbook using the SaveAs Method where I also mentioned the Path and named it Applying Set Object.
- Next, click on the Save button and go back to your worksheet.
- Then, go to the Developer tab >> click on Macros.
- Now, the Macros box will appear.
- Next, select Applying_Set_Object_Create_New_Workbook_with_Name.
- After that, click on Run.
- Finally, you will create a new workbook with the name as Applying Set Object.
3. Using Command Button by Creating and Naming New Workbook
We can also create and name a new workbook by using a Command Button in Excel VBA. Clicking on the Command Button you can execute a procedure working on that worksheet.
Follow the steps given below to do it on your own.
Steps:
- Firstly, go to the Developer tab >> click on Insert >> select Command Button from ActiveX Controls.
- Then, insert a Command Button in the worksheet and Right-click on it.
- After that, click on CommandButton Object >> select Edit.
- Next, type Create New Workbook as the name of the Command Button.
- Now, a Command Button is inserted into the worksheet.
- Again, right-click on the Command Button.
- Then, click on View Code.
- After that, write the following code.
Private Sub CommandButton1_Click()
Dim New_Workbook As Workbook
Set New_Workbook = Workbooks.Add
   New_Workbook.SaveAs Filename:="F:\Using Command Button.xlsx"
End Sub
Code Breakdown
- Firstly, we created a Private Sub Procedure as CommandButton1_Click().
- Next, we declared New_Workbook as Workbook.
- Then, we created a new workbook using the Add Method and set it as New_Workbook.
- After that, we saved the workbook using the SaveAs Method where I also mentioned the Path and named it Using Command Button.
- Next, click on the Save button and go back to your worksheet.
- Then, click on Design Mode from the Developer tab.
- Now, click on the Command Button.
- Finally, you will create a new workbook with the new name Using Command Button.
4. Use of Copy Method to Create New Workbook by Copying an Existing Worksheet
In the fourth method, we will show how to create a new workbook by copying the active worksheet and naming it according to your preference by using the Copy method in Excel VBA.
Steps:
- In the beginning, go to the Developer tab >> select Visual Basic.
- Then, insert a module going through the step shown in Method 1.
- After that, write the following code in your Module.
Sub Use_of_Copy_Method()
   Dim New_Workbook As Workbook
   Worksheets("Use of Copy Method").Copy
   Set New_Workbook = ActiveWorkbook
   New_Workbook.SaveAs Filename:="F:\Use of Copy Method.xlsx"
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Use_of_Copy_Method().
- Next, we declared New_Workbook as Workbook.
- Now, we copied the worksheet using the Copy Method.
- Then, we set the New_Workbook as Active Workbook .
- After that, we saved the workbook using the SaveAs Method where I also mentioned the Path and named it Use of Copy Method.
- Next, save the module following the step shown in Method 1 and go back to your worksheet.
- Then, go to the Developer tab >> click on Macros.
- Now, the Macros box will appear.
- Next, select Use_of_Copy_Method.
- After that, click on Run.
- Finally, you will be able to create a new workbook with the name as Use of Copy Method.
5. Using Move Method to Create New Workbook by Moving an Existing Worksheet
Now, we will show how to create a new workbook by moving the active worksheet and naming it according to your preference by using the Move method in Excel VBA.
Follow the steps given below to do it on your own.
Steps:
- Firstly, go to the Developer tab >> select Visual Basic.
- Then, insert a module going through the step shown in Method 1.
- After that, write the following code in your Module.
Sub Using_Move_Method()
   Dim New_Workbook As Workbook
   Worksheets("Using Move Method").Move
   Set New_Workbook = ActiveWorkbook
   New_Workbook.SaveAs Filename:="F:\Using Move Method.xlsx"
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Using_Move_Method().
- Next, we declared New_Workbook as Workbook.
- Now, we used the Move Method to move this worksheet.
- Then, we set the New_Workbook as Active Workbook .
- After that, we saved the workbook using the SaveAs Method where I also mentioned the Path and named it Using Move Method.
- Next, save the module following the step shown in Method 1 and go back to your worksheet.
- Then, go to the Developer tab >> click on Macros.
- Now, the Macros box will appear.
- Next, select Using_Move_Method.
- After that, click on Run.
- Finally, you will be able to create a new workbook with the name as Using Move Method.
6. Applying Range Object to Create a New Workbook and Naming it According to Cell Value
In the final method, we will show you how to create a new workbook and name it according to a cell value from the active worksheet by using the Range object in Excel VBA. Go through the steps given below to do it on your own.
Steps:
- In the beginning, go to the Developer tab >> select Visual Basic.
- After that, insert a module going through the step shown in Method 1.
- Now, write the following code in your Module.
Sub Applying_Range_Object()
Dim New_Workbook As String
New_Workbook = ActiveWorkbook.Sheets(6).Range("B2").Value
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=New_Workbook & ".xls"
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Applying_Range_Object().
- Next, we declared New_Workbook as String.
- Now, we used the Value method to set New_Workbook value as the value of Cell B2 from the Active Workbook.
- Then, we added a new workbook using the Add Method.
- After that, we saved the workbook using the SaveAs Method and selected the name as New_Workbook.
- Next, save the module following the step shown in Method 1 and go back to your worksheet.
- Then, go to the Developer tab >> click on Macros.
- Now, the Macros box will appear.
- After that, select Applying_Range_Object.
- Then, click on Run.
- Finally, you will be able to create a new workbook and name it as the Text in Cell B2 from the active sheet.
Read More: Excel VBA Calculate Workbook
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Download Practice Workbook
Conclusion
So, in this article, you will find 6 ways to create a new worksheet and name it using VBA in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.
Get FREE Advanced Excel Exercises with Solutions!
