Hi.
It would be very nice if SheetsInNewWorkbook was one of the arguments for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C

Well, you could do Workbooks.Add followed by the requisite number of
Worksheets.Add.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Hi.
It would be very nice if SheetsInNewWorkbook was one of the arguments for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C

Yeah, but thats not very cool. Its like a WorkAround.
I was thinking something in the lines of class modules, which I am not very
familiar with.

"Jon Peltier" wrote:

Well, you could do Workbooks.Add followed by the requisite number of
Worksheets.Add.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Hi.
It would be very nice if SheetsInNewWorkbook was one of the arguments for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C

Hi Albert,

Why not simply change SheetsInNewWorkbook if you need to and reset as
original.

Sub test()
Dim wbNew As Workbook

AddNewBook wbNew, 6

MsgBox wbNew.Worksheets.Count & " worksheets", , wbNew.Name

End Sub

Sub AddNewBook(wb As Workbook, Optional nSheets As Long = 0)
Dim nSheetsDefault As Long

On Error GoTo errExit
If nSheets 0 Then
nSheetsDefault = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = nSheets
End If

Set wb = Application.Workbooks.Add

errExit:
If nSheetsDefault 0 Then
Application.SheetsInNewWorkbook = nSheetsDefault
End If
End Sub

Regards,
Peter T
"Albert" wrote in message
...
Hi.
It would be very nice if SheetsInNewWorkbook was one of the arguments for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C

Half of programming in Excel is a workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Yeah, but thats not very cool. Its like a WorkAround.
I was thinking something in the lines of class modules, which I am not
very
familiar with.

"Jon Peltier" wrote:

Well, you could do Workbooks.Add followed by the requisite number of
Worksheets.Add.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Albert" wrote in message
...
Hi.
It would be very nice if SheetsInNewWorkbook was one of the arguments
for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C

Hi John,

Your suggestion and example code seems pretty much the same as I had posted
earlier, except it omits to trap user's original application setting and
resets to a notional SheetsInNewWorkbook = 3

I know '3' is Excel's default install but users can and do change this in
Tools Options General, not infrequently to '1' and no doubt to other
values.

Regards,
Peter T

"John Bundy" wrote in message
...
Or the easy way, change it to what you want then change it back:

On Error Resume Next
Application.SheetsInNewWorkbook = 16
Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3

-John

"Albert" wrote:

Very cool stuff sir.
Thanx!

"Peter T" wrote:

Hi Albert,

Why not simply change SheetsInNewWorkbook if you need to and reset as
original.

Sub test()
Dim wbNew As Workbook

AddNewBook wbNew, 6

MsgBox wbNew.Worksheets.Count & " worksheets", , wbNew.Name

End Sub

Sub AddNewBook(wb As Workbook, Optional nSheets As Long = 0)
Dim nSheetsDefault As Long

On Error GoTo errExit
If nSheets 0 Then
nSheetsDefault = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = nSheets
End If

Set wb = Application.Workbooks.Add

errExit:
If nSheetsDefault 0 Then
Application.SheetsInNewWorkbook = nSheetsDefault
End If
End Sub

Regards,
Peter T
"Albert" wrote in message
...
Hi.
It would be very nice if SheetsInNewWorkbook was one of the
arguments for
the Workbooks.Add procedure.
Maybe I can do this somehow through my PersonalMacros Addin?
Best regards,
Albert C