Add Workbook with specific number of worksheets

[Linda Mac]

I want to do a Workbook.Add to add a new workbook and I
want it to have 4 worksheets in the workbook. How can I
force it to have 4 worksheets?

 

[Bob Phillips]

Hi Linda,

It's not hard

Application.SheetsInNewWorkbook = 4
Workbooks.Add


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Hi linda

Try this

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

 

[Tom Ogilvy]

numSh = application.SheetsInNewWorkbook
application.SheetsInNewWorkbook = 4
Workbooks.Add
application.SheetsInNewWorkbook = numSh

 

[Ron de Bruin]

You can remove the line Linda
Dim a As Integer