Hi,
I have some code which creates a workbook. I want the workbook to have
only 1 sheet. The Workbooks.Add command performs the default behavior
and creates 3 sheets per workbook.
Is there any way to do this short of creating the workbook, then
deleting sheets 2 and 3 via a macro or code.

Thanks,
Harsh.

Application.SheetsInNewWorkbook = 1

HTH
--
AP

"aerotops" a écrit dans le message de news:
...
Hi,
I have some code which creates a workbook. I want the workbook to have
only 1 sheet. The Workbooks.Add command performs the default behavior
and creates 3 sheets per workbook.
Is there any way to do this short of creating the workbook, then
deleting sheets 2 and 3 via a macro or code.

Thanks,
Harsh.

You can use this to create a workbook with one sheet without changing the SheetsInNewWorkbook

Workbooks.Add(xlWBATWorksheet)


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Ardus Petus" wrote in message ...
Application.SheetsInNewWorkbook = 1

HTH
--
AP

"aerotops" a écrit dans le message de news: ...
Hi,
I have some code which creates a workbook. I want the workbook to have
only 1 sheet. The Workbooks.Add command performs the default behavior
and creates 3 sheets per workbook.
Is there any way to do this short of creating the workbook, then
deleting sheets 2 and 3 via a macro or code.

Thanks,
Harsh.

Thanks guys. I tired to use Ron's method of:
Workbooks.Add(xlWBATWorksheet)
I don't know what xlWBATWorksheet is but I included that line in my
code and it works. But it also opens up a new workbook for some reason.
Any thoughts why and how to stop it from doing that?

Ardus suggested Application.SheetsInNewWorkbook = 1

If I implement that, is this something like
Application.DisplayAlerts=Flase and just like we set
Application.DisplayAlerts = True at the end of the sub, would I have to
set:

Application.SheetsInNewWorkbook = 3 at the end of my set to go back to
Excel's default behavior.

Harsh.

But it also opens up a new workbook for some reason

Yes a new workbook with one sheet

have some code which creates a workbook. I want the workbook to have
only 1 sheet

It is doing what you want

--
Regards Ron De Bruin
http://www.rondebruin.nl



"aerotops" wrote in message oups.com...
Thanks guys. I tired to use Ron's method of:
Workbooks.Add(xlWBATWorksheet)
I don't know what xlWBATWorksheet is but I included that line in my
code and it works. But it also opens up a new workbook for some reason.
Any thoughts why and how to stop it from doing that?

Ardus suggested Application.SheetsInNewWorkbook = 1

If I implement that, is this something like
Application.DisplayAlerts=Flase and just like we set
Application.DisplayAlerts = True at the end of the sub, would I have to
set:

Application.SheetsInNewWorkbook = 3 at the end of my set to go back to
Excel's default behavior.

Harsh.

Hi,
Yes it is doing what I want. I guess I phrased the question wrong. Now
I see what you are trying to say. I was thinking more on the lines of
the fact that I have already created a new sheet and some way to set
its number of sheets to 1. But there is no reason not to use the
workbook created by your function call. Thanks for the clarification.
Harsh.

Ron de Bruin wrote:
But it also opens up a new workbook for some reason

Yes a new workbook with one sheet

have some code which creates a workbook. I want the workbook to have
only 1 sheet

It is doing what you want

--
Regards Ron De Bruin
http://www.rondebruin.nl



"aerotops" wrote in message oups.com...
Thanks guys. I tired to use Ron's method of:
Workbooks.Add(xlWBATWorksheet)
I don't know what xlWBATWorksheet is but I included that line in my
code and it works. But it also opens up a new workbook for some reason.
Any thoughts why and how to stop it from doing that?

Ardus suggested Application.SheetsInNewWorkbook = 1

If I implement that, is this something like
Application.DisplayAlerts=Flase and just like we set
Application.DisplayAlerts = True at the end of the sub, would I have to
set:

Application.SheetsInNewWorkbook = 3 at the end of my set to go back to
Excel's default behavior.

Harsh.