Have you been in that situation where you are supposed to open or create a new workbook using VBA in Excel? From time to time, we are faced with work that require us to create a workbook, and we can easily do this using VBA. This post looks at how one can create a new sheet VBA in Excel.
Procedure of creating Excel worksheet with VBA
Step 1: Open an Excel workbook
The very first thing we need to do in order to add a VBA sheet in Excel is to open a workbook. To open, just as it is with every other workbook, we simply need to double-click on it. Alternatively, we can right-click on the workbook and select “Open”.
Step 2: Open VBA editor
After we have opened an Excel workbook, the next thing to do is to open the Excel VBA editor. To do this, press Alt + F11.
Step 3: Insert new module
From the Insert menu, click on new to open a new module
Figure 1: VBA new module
Step 4: Copy and paste the code
In the new module, copy and paste the code below;
Sub AddNewWorkbook1()
'Adding New Workbook
Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs "C:\WorkbookName.xls"
'OR
ActiveWorkbook.SaveAs Filename:="C:\WorkbookName1.xls"
End Sub
Step 5: Check output
In order to see the output of the code, you need to press F5 key.
The output will be a newly created workbook by VBA. Now save the workbook as macro enabled workbook.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment