+ Reply to Thread
Results 1 to 5 of 5

# of sheets in a new workbook

  1. #1
    JT
    Guest

    # 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

  2. #2
    Chip Pearson
    Guest

    Re: # 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Ron de Bruin
    Guest

    Re: # 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" <[email protected]> wrote in message news:[email protected]...
    > 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




  4. #4
    Jim Thomlinson
    Guest

    RE: # 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


  5. #5
    Dave Peterson
    Guest

    Re: # 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1