How do I delete named sheets within a work book

[TerryK]

I have a changes log and a a sheet of table values used in a template that I want to delete from the sheet after I've saved it so it can be sent to a client without extra stuff they aren't interested in (or even perhaps could be:wink: . Can anyone give me any pointers? Recorded code asks for confirmation so how do I get the macro to just do it!

Thanks in anticipation.

Terry

 

[Andrew Poulsom]

This:

Application.DisplayAlerts = False

will suppress the confirmation dialog. Make sure you set it back to True when done.

 

[TerryK]

Excel crashes everytime

When I run the following code it falls over when it gets towards the end and I try to delete sheets and then a button and drop down list box and then clear some formatting by deleting a range of cells. It fails before the macros are deleted.

Here is the code

Sub SetForSave()
'
' SetForSave Macro
' Macro recorded 15/08/2006 by Terry Kinnard
'
Dim result
' Make all formulae into fixed values
' Site Name
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Date
Range("C13").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Address Range
Range("C7:C11").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Branch Contact
Range("C32").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Branch Fax number
Range("F32").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Select Case Range("k13").Value
Case 10
ChDrive ("P")
ChDir ("\10_Service\Clients\Debenhams\Quotes")
Case 20
ChDrive ("P")
ChDir ("\20_Serv\20_admin\LJN\DEBENHAMS")
Case 30
ChDrive ("P")
ChDir ("\10_Service\Clients\Debenhams\Quotes")
Case 40
ChDrive ("P")
ChDir ("\40_Service\Debenhams\Quotes")
End Select

ActiveWorkbook.SaveAs Filename:= _
Range("C15").Value, FileFormat:=xlNormal, _
CreateBackup:=True

' Turn alerts off while we delete sheets
Application.DisplayAlerts = False
' on error goto SetForSaveError

' Remove extra sheets and clear button and coloured area on main sheet.
Sheets("Change Log").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Tables").Select
ActiveWindow.SelectedSheets.Delete

' Remove the drop down list box and the command buttoN
Sheets("DEBENHAMS QUOTATION").Select

ActiveSheet.Shapes("cbSetForSave").Select
Selection.Delete
ActiveSheet.Shapes("ddSiteList").Select
Selection.Delete

' Turn alerts back on again after deleting sheets
Application.DisplayAlerts = True

Range("H6:R11").Select
Selection.Delete Shift:=xlToLeft

' Now get rid of the macros
Call RemoveAllMacros(ActiveWorkbook)

' Call TurnOnScreenUpdates
Exit Sub

SetForSaveError:
' Process error so that we see the error code and error message associated with the error
result = MsgBox("Error in procedure SetForSave Error code = " & Err.Number & " Error Description = " & Err.Description & " Error Source = " & Err.Source, vbOKOnly, "Trapped Error")

Resume Next

End Sub


Thanks for any help you can give. We are still using Excel 2000.

Regards

Terry