Free Excel Training

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

Add and save a new work book using VBA in Excel
Creating workbook using a macro