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