How to Use Macros to Create Excel Workbooks
You may sometimes want or need to create an Excel workbook in an automated way. For instance, you may need to copy data from a table and paste it into a newly created workbook. The following macro copies a range of cells from the active sheet and pastes the data into a new workbook.
How the macro works
As you’ll see when you read through the lines of the code, this macro is relatively intuitive:
Sub Macro1() 'Step 1 Copy the data Sheets("Example 1").Range("B4:C15").Copy 'Step 2 Create a new workbook Workbooks.Add 'Step 3 Paste the data ActiveSheet.Paste Destination:=Range("A1") 'Step 4 Turn off application alerts Application.DisplayAlerts = False 'Step 5 Save the newly created workbook ActiveWorkbook.SaveAs _ Filename:="C:TempMyNewBook.xlsx" 'Step 6 Turn application alerts back on Application.DisplayAlerts = True End Sub
In Step 1, you simply copy the data that ranges from cells B4 to C15. Note that you specify both the sheet and the range by name. This approach is a best practice when working with multiple open workbooks.
In Step 2, you use the Add method of the Workbook object to create a workbook. The blank workbook is equivalent to manually choosing File→New→Blank Document in the Excel ribbon.
In Step 3, you use the Paste method to send the copied data to cell A1 of the new workbook.
Pay attention to the fact that the code refers to the ActiveSheet object. When you add a workbook, the new workbook immediately gains focus, becoming the active workbook. (Excel does the same when you add a workbook manually.)
In Step 4 of the code, you set the DisplayAlerts method to False, effectively turning off Excel’s warnings. You do this because in the next step of the code, you save the newly created workbook. You may run this macro multiple times, in which case Excel attempts to save the file multiple times.
What happens when you try to save a workbook multiple times? That’s right — Excel warns you that there is already a file with that name and then asks if you want to overwrite the previously existing file. Because your goal is to automate the creation of the workbook, you want to suppress that warning.
In Step 5, you save the file by using the SaveAs method. Note that you enter the full path of the save location, including the final filename.
Because you turned off application alters in Step 4, you need to turn them back on (see Step 6). If you don’t, Excel continues to suppress all warnings during the current session.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
Activate Visual Basic Editor by pressing Alt+F11.
Right-click the project/workbook name in the project window.
Type or paste the code in the newly created module.
You’ll probably need to change the sheet name, the range address, and the save location.