Create new workbooks

With the function below you can create new workbooks with up to 255 new worksheets. You can use the macro like this if you want to create a new workbook with 10 worksheets:
Set wb = NewWorkbook(10)

Function NewWorkbook(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
    Set NewWorkbook = Nothing
    If wsCount < 1 Or wsCount > 255 Then Exit Function
    OriginalWorksheetCount = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = wsCount
    Set NewWorkbook = Workbooks.Add
    Application.SheetsInNewWorkbook = OriginalWorksheetCount
End Function

Document last updated 1999-12-20 12:51:17

User comments:

Ole P. from Norway wrote (2005-10-16 22:51:24 CET):

Re: Writing to the new workbook
Either like this:
Worksheets("MySheetName").Activate
Range("A1").Formula = "Some Text"
Range("A2").Formula = 9999

Or like this:
With Worksheets("MySheetName")
.Range("A1").Formula = "Some Text"
.Range("A2").Formula = 9999
End With

BawinJata from Fort Myers USA wrote (2005-10-15 20:10:42 CET):

Writing to the new workbook
If I created the new workbook from a template that has named sheets, how do I refer to the sheets so I can write to them?

Ole P. from Norway wrote (2005-09-22 00:09:06 CET):

Re: How do I add a name to the new workbook?
This will define a workbook level name in the first sheet in the new workbook:
Set wb = NewWorkbook(10)
wb.Names.Add Name:="MyRange", RefersTo:=Range("A1:D10")

Fred from LA, CA wrote (2005-09-21 19:56:45 CET):

How do I add a name to the new workbook?
You site is so helpful! Thanks very much.

I want to add a special name to the new workbook. How should I do that?

Thank you again.

steve ly from bolton uk wrote (2004-06-05 14:48:57 CET):

saved hours
i wa s look for a far more complicated answer this code did the job

A.Shora wrote (2004-05-18 13:42:11 CET):

Very Useful
I must agree it’s really cool. Thank you very much

K9Planner from Seattle Wa wrote (2003-11-02 22:58:10 CET):

This code is Cool
Thanks for the code. It willl come in handy at work. I have to compare data all the time from week to week and this will give me an edge.