There are many times when you need more than three sheets in a workbook. But when you use Workbooks.Add, it creates a New Workbook with three sheets (default for Microsoft Excel). Later you will add (or delete) the sheets for your use.
Here is another way to solve the problem. Use the Application’s SheetsInNewWorkbook property to set the default no. of worksheets
‘ coded for vbadud.blogspot.com by shasur
MsgBox “No of sheets in a blank workbook is : ” ; Application.SheetsInNewWorkbook
‘ set the Workbook for One Sheet
Application.SheetsInNewWorkbook = 1
‘ Workbook with only one sheet will be added
‘ Reset the Workbook for Five Sheets
Application.SheetsInNewWorkbook = 3
Explore upcoming VBA Batch nearby you and, learn to automate your routine Excel Reports, Processes and tasks with ease.