Creating a New Workbook in Excel VBA

Creating a New Workbook

The following VBA procedure demonstrates how to create and open a new workbook in Excel. To confirm that the new workbook has been successfully added, it displays the number of currently open workbooks before and after the operation:

Sub CreateNewWorkbook()
    MsgBox "Before: " & Workbooks.Count & " workbook(s)"
    Workbooks.Add
    MsgBox "After: " & Workbooks.Count & " workbook(s)"
End Sub

Explanation:

This procedure calls the Add() method of the Workbooks collection. The Add() method instructs Excel to create a new, empty workbook and open it. As a result, this new file becomes the active workbook, meaning it is now in focus and ready for editing or further automation.

Simultaneously, the Workbooks collection is updated to include this new workbook. You can clearly observe this change by looking at the two message boxes: one shows the number of workbooks before creation, and the other shows the count afterward. The difference confirms that a new workbook has been added to the collection.

This approach is useful when your macro needs to generate a new workbook—for example, to export reports, create backups, or collect data—without altering the currently open files.