Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Thanked 0 Times in 0 Posts

    multisheet workbook (windowsXP officeXP pro 2002)

    a friend of mine found the following on the web, whilst looking for a solution on how to create a multi sheet work book, it's been driving him and me bananas on how to get it to work, we would like to create 20-30 worksheets at this particular time.
    __________________________________________________ _______________________________
    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
    can any one suiggest what to do in order for it to run? <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Thanked 7 Times in 7 Posts

    Re: multisheet workbook (windowsXP officeXP pro 2002)

    Firstly you need to put the code into a VBA Module.
    <UL><LI>Start Excel
    <LI>Type Alt-F8 to show the Macros window
    <LI>Type TestIt and click Add to create a new Macro called TestIt
    <LI>Add a single line to the TestIt macro like this
    Sub TestIt()
    NewWorkbook wsCount:=10
    End Sub
    <LI>Then copy the your NewWorkbook code and paste that into the same VBA module.
    <LI>Now you can run TestIt to create a workbook with 10 worksheets.[/list]StuartR

Posting Permissions

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