Create or Add a New Workbook using Excel VBA

Create or Add a New Workbook using Excel VBA

In your routine workflow, you often come across situations where  you want or need to create a new workbook in an automated way, and only way to get this done is creating the New Workbook using Excel VBA

For instance, you might want  to copy data from a table or section or sheet and paste it into a new workbook and you need to do this very frequently then doing it manually can be painful and time consuming, Excel VBA is best way to achieve this task

The following macro copies a range of cells from the active sheet and pastes the data into a new workbook

Ready to use VBA Code, just copy and paste

Sub Macro1()

' Step 1 Copy the data, change the sheet name and range you would like to copy to new workbook
Sheets("Sheet1").Range("A1:C15").Copy

' Step 2 Create a new workbook
Workbooks.Add

' Step 3 Paste the data, you might want to change the destination range as well
ActiveSheet.Paste Destination:=Range("A1")

' Step 4 Turn off application alerts
Application.DisplayAlerts = False

' Step 5 Save the newly created workbook, please change the path before running the code
ActiveWorkbook.SaveAs Filename:="C:\Users\Desktop\MyNewBook.xlsx"

' Step 6 Turn application alerts back on
Application.DisplayAlerts = True

End Sub

How the code works?

  1. In Step 1, we simply copy the data that ranges from cells A1 to C15.
  2. The thing to note here is that you are specifying both the sheet and the range by name. This is a best practice when you are working with multiple workbooks open at one time.
  3. We are using the Add method of the Workbook object to create a new workbook. This is equivalent to manually clicking File⇒New⇒Blank Document in the Excel Ribbon.
  4. In this step, you use the Paste method to send the data you copied to cell A1 of the new workbook.
  5. 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. This is the same behavior you would see if you were to add a workbook manually.
  6. In Step 4 of the code, we set the DisplayAlerts method to False, effectively turning off Excel’s warnings. We do this because in the next step of the code, we save the newly created workbook. We may run this macro multiple times, in which case Excel attempts to save the file multiple times.
  7. What happens when you try to save a workbook multiple times? That’s right — Excel warns you that there is already a file out there 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 new workbook, you want to suppress that warning.
  8. In Step 5, we save the file by using the SaveAs method. Note that we are entering the full path of the save location, including the final filename.
  9. Because we turned application alters off in Step 4, we need to turn them back on. If we don’t, Excel continues to suppress all warnings for the life of the current session.

How to use it

To use this macro, you can copy and paste it into a standard module:

  • Activate the Visual Basic Editor by pressing ALT+F11.
  • Right-click the project/workbook name in the Project window.
  • Choose Insert⇒Module.
  • Type or paste the code in the newly created module. You will need to change the sheet name, the range address, and the save location.
How to Add New code module in Excel-1

Also check out my blog post on how to save workbook when a cell or range of cells changes automatically using Excel VBA here

If you want to master Excel in Professional way, check out my FREE EBook available for download, this Ebook contains more of these ready to apply Excel VBA codes and formulas! (Find the form below to get this awesome Ebook for FREE)

If you have any question or query regarding above code usage, let me know in the comments below! 

FREE Advanced Excel Ebook

Get This Amazing PRO Excel & Excel VBA Tips and Tricks Ebook worth $ 30 for FREE ! Apply Ready to Use Formulas and VBA code to your requirements and work faster and smarter!

Share on email
Share on facebook
Share on twitter
Share on linkedin
Share on pinterest

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *

FREE Advanced Excel Ebook

GET Amazing 15 PRO Excel Tips and Tricks EBOOK for free! Let me know where to send it?

FREE Course Access

Master Depreciation Accounting with Advance Excel Template