How do I use Workbooks.Add

[Newboy18]

Hi, I wanted to use Workbooks.Add to create a new, blank
output workbook but it always creates it with the default
3 sheets, how can I create it with just 1 sheet.
The finished Workbook will have more sheets added later in
the routine but the workbook will be used by others that
may have the default set to a different value

 

[Chip Pearson]

Try something like

Dim NewSheets As Integer
NewSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = NewSheets


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Newboy18]

Thanks, that was the fastest reply I ever had.
Turns out that all I needed was the 1 line

 

[Tom Ogilvy]

The command sets the users options in Tools=>Options to 1, so now whenever
they add a workbook, it will have 1 sheet. The reason Chip gave you the
additional lines of code was so you would reset the number of sheets back to
what the user originally had. Not using all the code would result in
extremely ill behaved code.

An easier way to do it without changing the users settings is:

Workbooks.Add xlWBATWorksheet

This will add a one sheet workbook.