# of sheets in a new workbook
 

When you programmatically add a new workbook, is it possible to indicate the
number of sheets the new workbook should have?

Thanks for the help.......
--
JT

# of sheets in a new workbook
 

JT,

Try something like

Application.SheetsInNewWorkbook = 4


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"JT" wrote in message
...
When you programmatically add a new workbook, is it possible to
indicate the
number of sheets the new workbook should have?

Thanks for the help.......
--
JT

# of sheets in a new workbook
 

Hi JT

Try this

Sub test()
Dim OldValue As Integer
OldValue = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 6
Workbooks.Add
Application.SheetsInNewWorkbook = OldValue
End Sub


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


"JT" wrote in message ...
When you programmatically add a new workbook, is it possible to indicate the
number of sheets the new workbook should have?

Thanks for the help.......
--
JT

# of sheets in a new workbook
 

Here is some code...

Sub test()
Dim wbk As Workbook
Dim intSheets As Integer
On Error GoTo Errorhandler

intSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 4
Set wbk = Workbooks.Add
Errorhandler:
Application.SheetsInNewWorkbook = intSheets
End Sub

As always when changing application level settings it is best to use an
error handler
--
HTH...

Jim Thomlinson


"JT" wrote:

When you programmatically add a new workbook, is it possible to indicate the
number of sheets the new workbook should have?

Thanks for the help.......
--
JT

# of sheets in a new workbook
 

Another option...

You can add a workbook with just one sheet and add as many as you want later.

dim newWkbk as workbook
dim iCtr as long
set newwkbk = workbooks.add(1)
for ictr = 1 to 99
newwkbk.worksheets.add
next ictr

I'd end up with 100 worksheets in that new workbook.

JT wrote:

When you programmatically add a new workbook, is it possible to indicate the
number of sheets the new workbook should have?

Thanks for the help.......
--
JT

--

Dave Peterson