Adding And Deleting Sheets
 

I currently have a code that the user is prompted for a number, and then that
number of sheets is added to the workbook. My boss came and looked at it,
and he would like the program to do the following: With the same prompt, if
the user would like fewer sheets, they can enter how many they want, and the
program will delete any extra sheets with a warning to the user. Or, the
program will add additional sheets. I hope this isn't confusing. Any help
anyone can give me would be appreciated. I'm just getting started on this
today, so I don't have a code yet.

Thank you!!

Adding And Deleting Sheets
 

That 'delete worksheets' after they've been added sounds like a bad idea.
Suggest you don't create the worksheets to begin with. Here's a procedure I
put together some time ago called 'InsertMultipleWorksheets'.
Hope it helps.
Sincerely,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

'/================================================== =/
Public Sub InsertMultipleWorksheets()
'
' Gary L. Brown
' 12/17/2001
'
'add multiple worksheets to a workbook at one time
' AFTER the active worksheet
'
Dim lngSheets2Add As Long, lngInMsg As Long
Dim iMax As Integer
Dim x As Integer
Dim strCurrentWorksheet As String
Dim varAnswer As Variant

'only allow max of iMax sheets to be added at one time
iMax = 100

'check for an active workbook
If ActiveWorkbook Is Nothing Then 'no workbooks open, so create one
Workbooks.Add
End If

'remember current worksheet so you can come back to it after
' worksheets have been added
strCurrentWorksheet = Application.ActiveSheet.name

Do
'get # of worksheets to be added
lngSheets2Add = 0
lngSheets2Add = _
Application.InputBox(Prompt:="Enter # of Sheets to be added:", _
Title:="Add Multiple worksheets at-a-time...", _
Default:=0, Type:=1)
lngInMsg = lngSheets2Add

'check to see if too many worksheets are being requested
If lngSheets2Add iMax Then
lngSheets2Add = iMax
varAnswer = MsgBox("Only " & Format(iMax, "#,##0") & _
" worksheets may be added at one time." & vbCr & vbCr & _
Format(iMax, "#,##0") & " worksheets will be added." & _
vbCr & vbCr & "Continue?...", _
vbInformation + vbYesNo + vbDefaultButton2, _
"Warning...You have requested " & Format(lngInMsg, "#,##0") & _
" worksheets to be added...")
If varAnswer = vbNo Then
MsgBox "Insert has been halted by user.", _
vbExclamation + vbOKOnly, "Warning..."
Exit Sub
End If
End If

'cancel if no sheets are requested
If lngSheets2Add = 0 Then
MsgBox "Zero (0) sheets have been requested." & _
vbCr & vbCr & "Worksheet Insert has been canceled...", _
vbInformation + vbOKOnly, "Warning..."
Exit Sub
End If

'check if you want to continue
If MsgBox("Continue?", _
vbInformation + vbYesNo + vbDefaultButton1, _
"Insert " & lngSheets2Add & " worksheets?") = vbYes Then
Exit Do
End If
Loop

For x = 1 To lngSheets2Add
Sheets.Add After:=Application.ActiveSheet
Next x

Application.Worksheets(strCurrentWorksheet).Activa te

End Sub
'/================================================== =/



"Anice" wrote:

I currently have a code that the user is prompted for a number, and then that
number of sheets is added to the workbook. My boss came and looked at it,
and he would like the program to do the following: With the same prompt, if
the user would like fewer sheets, they can enter how many they want, and the
program will delete any extra sheets with a warning to the user. Or, the
program will add additional sheets. I hope this isn't confusing. Any help
anyone can give me would be appreciated. I'm just getting started on this
today, so I don't have a code yet.

Thank you!!