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