Input Number of Worksheets

Posted by BB on January 15, 2002 7:45 AM

Does anyone have a macro that if run will ask the user how many worksheets they want built for the current workbook?

Thanks in advance!!

Posted by Jerid on January 15, 2002 8:41 AM

Try this

Sub AddNewSheets()
On Error GoTo ErrHandler

Dim iNumSheets As Integer

iNumSheets = CInt(InputBox("Enter the number of sheets you wish to create (1-255)"))
Application.Worksheets.Add Count:=iNumSheets

ExitHandler:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 13
MsgBox "You must enter a number between 1 and 255", vbCritical, "Custom Error Message"
Resume
Case Else
MsgBox "An Error has occured!" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical, "Contact the application developer"
End Select
End Sub

Jerid