Thursday, April 17, 2008

Set No Of Sheets in a Workbook using Excel VBA

Change default number of Sheets using Excel VBA

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

Sub Set_No_Of_Sheets()

' -----------------------------------------
' 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
Workbooks.Add


' Reset the Workbook for Five Sheets
Application.SheetsInNewWorkbook = 3

End Sub

Here is a way suggested by
Jon Peltier, Microsoft Excel MVP (http://PeltierTech.com)

Workbooks.Add([Template])

Template is optional, but if you use one of these constants, it creates a workbook with a single sheet of the type defined by the contant: Many thanks Jon for your suggestion

xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or
xlWBATWorksheet

This command then creates a workbook with a single worksheet:

Workbooks.Add xlWBATWorksheet


Many thanks Jon for your valuable suggestion

No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group