VBA to create new Excel Workbook

You can easily add any type of workbook, whether it’s a .xlsm, xlsx, xlst etc., using the Method workbooks.add

Using this vba method, one can achieve as follows:

You can add a workbook.add method to a variable:

Dim wb as workbook
set wb=workbooks.add 

can process creating a new workbook and manipulate the created workbook as desired. name a workbook and save file.

To do this we have to proceed with the following code:

Public Sub createnewWorkBook()
     Dim newWorkbook as object
     Set newWorkbook = Workbooks.Add
    With newWorkbook
        .Title = "Set workbook title - displayed in Info > Properties"
        .Subject = "Subject related  - displayed in Info > Properties"
        .SaveAs Filename:="C:\Users\SAFT\Desktop\New workbook.xlsx" 'name a file
        .Close
    End With
End Sub

You can even use Workbooks.Add method to copy data from a workbook to a new worksheets.

Public Sub extractColumn()
Set range1 = Range("A:F, BI:GI, BQ:CQ,CL:CL,CM:CN,CT:CT,DM:DM")
range1.Copy
Set newbook = Workbooks.Add
ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

Syntax of Workbooks.Add Method

Workbooks.Add(Template as Object)

Template Optional Object. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template.

Ex:

The new workbook is created with the specified file as a template:

Dim strTemplate As String: strTemplate = "c:\temp\myfile.xlsm"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)

The new workbook is a full copy of the file which served as template – including VBA code if any. The new workbook however does not yet exist in the file system.

The new workbook’s name will be after the template with a sequential number added, e.g. here ‘myfile1’.

If this argument is a constant, the new workbook contains a single sheet of the specified type.Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet.
If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property). If the Template argument specifies a file, the file name can include a path.

Remarks

Workbooks.Add is a Method (a Function of an Object), it returns a Workbook Object. You can visually see a workbook opened after using the Method, and you can also define a workbook Object to get the returned Workbook.

Set NewBook = Workbooks.Add