multisheet workbook (windowsXP officeXP pro 2002)

Topic

New Reply

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 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?

Reply | Quote

Replies

Firstly you need to put the code into a VBA Module.

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

Reply | Quote

Firstly you need to put the code into a VBA Module.

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

Reply | Quote