Create New Workbook VBA

Create a new workbook using a macro

  1. Click on Tools in the menu bar. From the Macro option select Visual Basic.
  2. In the Visual Basic Editor menu bar click on insert and then Module.
  3. First define a macro name like AddNew()
  4. We then assign it to an object variable called NewBook
  5. In our example, the Excel Workbook object returned by the Add method is assigned to the object variable, NewBook.
  6. Next, several properties of NewBook are set using the ‘With’ block. Inside the block you can easily control the new Excel workbook properties using the object variable.
  7. The ‘SaveAs’ part of the code will save the file in your default folder mostly MyDocuments and you can see the filename in the title bar of the workbook when you run the macro.
  8. Watch the video to see how the macro is implemented. Also, the code is given below for your reference.

Sub AddNew()

Set NewBook = Workbooks.Add

With NewBook

.Title = “Employee Details”

.Subject = “Employees”

.SaveAs Filename:=”Employee_Details.xls”

End With

End Sub

Further reading:

Add and Save new Workbook using VBA in Microsoft Excel