How To Create A New Workbook In Excel Vba

Creating a New Workbook in Excel VBA

To create a new workbook in Excel using VBA, you can use the Workbooks.Add method. This method adds a new workbook to the collection of workbooks and returns a reference to it. Here’s an example:

    Set wb = Workbooks.Add
  

In the above example, we are creating a new workbook and assigning it to the variable wb.

Example:

Let’s say we want to create a new workbook with two worksheets and save it with a desired name. Here’s an example code:

    Sub CreateNewWorkbook()
        Dim wb As Workbook
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        
        ' Create a new workbook
        Set wb = Workbooks.Add
        
        ' Rename the first worksheet
        Set ws1 = wb.Sheets(1)
        ws1.Name = "Sheet1"
        
        ' Rename the second worksheet
        Set ws2 = wb.Sheets(2)
        ws2.Name = "Sheet2"
        
        ' Save the workbook with a desired name
        wb.SaveAs "C:\path\to\desired\location\WorkbookName.xlsx"
        
        ' Close the workbook
        wb.Close
        
        ' Clean up memory
        Set ws1 = Nothing
        Set ws2 = Nothing
        Set wb = Nothing
    End Sub
  

In the above example, we first declare variables for the workbook and two worksheets. Then, we create a new workbook using the Workbooks.Add method. We rename the first worksheet as “Sheet1” and the second worksheet as “Sheet2” using the Name property. After that, we save the workbook with a desired name using the SaveAs method, specifying the desired file path and name. Finally, we close the workbook and clean up any remaining memory by setting the variables to Nothing.