How do I use Workbooks.Add
 

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

How do I use Workbooks.Add
 

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


"Newboy18" wrote in message
...
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

How do I use Workbooks.Add
 

Thanks, that was the fastest reply I ever had.
Turns out that all I needed was the 1 line
Application.SheetsInNewWorkbook = 1
Then my
Workbooks.Add

-----Original Message-----
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


"Newboy18" wrote in
message
...
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



.

How do I use Workbooks.Add
 

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.

--
Regards,
Tom Ogilvy

"Newboy18" wrote in message
...
Thanks, that was the fastest reply I ever had.
Turns out that all I needed was the 1 line
Application.SheetsInNewWorkbook = 1
Then my
Workbooks.Add

-----Original Message-----
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


"Newboy18" wrote in
message
...
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



.