Excel VBA Workbooks.Add Method to create new workbook

This page explains Excel VBA Workbooks.Add Method, illustrate how to create new workbook in Excel VBA or move data from one workbook to a new workbook

What does Excel VBA Workbooks.Add Method do?

Excel VBA Workbooks.Add Method is used to create new workbook. Suppose you have a workbook called workbook1, inside workbook1 you need to create new workbook and manipulate it, then you need to insert the Workbooks.Add Method in workbook1. After you have created the workbook, the workbook becomes activated, you can set a workbook title, save file, etc.

One common use of Workbooks.Add Method to copy data from a workbook to new worksheets in order to distribute different worksheets to different people.

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

Example 1 – Create new workbook and close it

The below code create a workbook called “New workbook”, and then set the Title and Subject for the workbook, finally save the workbook and close it. As soon as the newly created workbook is closed, the original workbook becomes activated again.

Public Sub createWB()
    Set newbook = Workbooks.Add
    With newbook
        .Title = "This title is displayed in Info > Properties"
        .Subject = "This subject is displayed in Info > Properties"
        .SaveAs Filename:="C:\Users\WYMAN\Desktop\New workbook.xlsx"
        .Close
    End With
End Sub

Example 2 – Move specific data to new workbook

This was a question originally asked in Microsoft Community and answered by me.

Question

I’m sure my issue is not unique. I have a excel document with hundreds of columns and only want about a dozen of them. I need to be able to extract specific

columns to a new excel sheet as a repeated process without manual intervention.

All I need is to pull certain columns into a new excel sheet from an excel document that is updated daily.

Do we have an automated process where I can just run a macro and pull the updated data from an excel document into a new one?

Any help is greatly appreciated.

Thank you.

Answer

The below code extracts specific columns in a workbook and then copy to a new workbook.

Public Sub extractCol()
     Set range1 = Range("A:D, BI:BI, BQ:BQ,CL:CL,CM:CN,CT:CT,DB:DB")
     range1.Copy
     Set newbook = Workbooks.Add
     ActiveCell.PasteSpecial Paste:=xlPasteValues
 End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.add.aspx