ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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      Printerfriendly version

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.

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address