But I want to create a new sheet from within my macro with just one sheet,
in disregard with the settings.

can anyone give the rigth syntax for this command?

The code below reads the current setting.  Opens a new book with one sheet
and resets the old setting.  This should get you started.

Sub insertOneSheet()
Dim oldSet As Integer
oldSet = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = oldSet
End Sub

--
HTH

Nick Hodge
Southampton, England

Workbooks.Add(xlWBATWorksheet)

...adds a new workbook based on Excel's internal single sheet template.

HTH

Rob

www.rb-ad.dircon.co.uk/xlhome.htm

: Peter
:
: The code below reads the current setting.  Opens a new book with one
sheet
: and resets the old setting.  This should get you started.
:
: Sub insertOneSheet()
: Dim oldSet As Integer
: oldSet = Application.SheetsInNewWorkbook
: Application.SheetsInNewWorkbook = 1
: Workbooks.Add
: Application.SheetsInNewWorkbook = oldSet
: End Sub
:
: --
: HTH
:
: Nick Hodge
: Southampton, England

:


: > When creating a new Workbook, the number of sheets in it  comes with
the
: > settings  made under Tools/Options.
: >
: > But I want to create a new sheet from within my macro with just one
sheet,
: > in disregard with the settings.
: >
: > can anyone give the rigth syntax for this command?
: >
:
:
:

this one is even shorter then the solution mentioned before and works as
well

Thanks a lot
k rgrds
Peter

: Peter,
:
: Workbooks.Add(xlWBATWorksheet)
:
: ...adds a new workbook based on Excel's internal single sheet template.