Excel VBA: Create a New Workbook and Name It (6 Examples)

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.


Download Practice Workbook


6 Ways to Create a New Workbook and Name It Using VBA in Excel

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.

Using Add Method to Create New Workbook and Name It 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.

Using Add Method to Create New Workbook and Name It in Excel VBA

  • 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.
  • Next, click on the Save button and go back to your worksheet.

Using Add Method to Create New Workbook and Name It in Excel VBA

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

Using Add Method to Create New Workbook and Name It in Excel VBA

  • Finally, you will be able to create a new workbook with a name as Using Add Method.

Read More: How to Add Sheet with Name from Cell Using Excel VBA


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.

Applying Set Object to Create New Workbook and Name It in Excel VBA

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

Applying Set Object to Create New Workbook and Name It in Excel VBA

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

Applying Set Object to Create New Workbook and Name It in Excel VBA

  • Finally, you will create a new workbook with the name as Applying Set Object.

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


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.

Using Command Button by Creating and Naming New Workbook

  • Then, insert a Command Button in the worksheet and Right-click on it.

  • After that, click on CommandButton Object >> select Edit.

Using Command Button by Creating and Naming New Workbook

  • Next, type Create New Workbook as the name of the Command Button.

  • Now, a Command Button is inserted into the worksheet.

Using Command Button by Creating and Naming New Workbook

  • 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

Using Command Button by Creating and Naming New Workbook

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.

Using Command Button by Creating and Naming New Workbook

  • Finally, you will create a new workbook with the new name Using Command Button.

Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)


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.

Use of Copy Method to Create New Workbook by Copying an Existing Worksheet

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

Use of Copy Method to Create New Workbook by Copying an Existing Worksheet

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

Use of Copy Method to Create New Workbook by Copying an Existing Worksheet

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


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.

Using Move Method to Create New Workbook by Moving an Existing Worksheet and Name it in Excel VBA

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

Using Move Method to Create New Workbook by Moving an Existing Worksheet and Name it in Excel VBA

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

Using Move Method to Create New Workbook by Moving an Existing Worksheet and Name it in Excel VBA

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


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.

Applying Range Object to Create a New Workbook and Name it According to Cell Value in Excel VBA

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

Applying Range Object to Create a New Workbook and Name it According to Cell Value in Excel VBA

  • Now, the Macros box will appear.
  • After that, select Applying_Range_Object.
  • Then, click on Run.

Applying Range Object to Create a New Workbook and Name it According to Cell Value in Excel VBA

  • Finally, you will be able to create a new workbook and name it as the Text in Cell B2 from the active sheet.

Applying Range Object to Create a New Workbook and Name it According to Cell Value in Excel VBA

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


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


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. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo