Yet another undesired pop-up

[Guest]

Hi All......

I recorded a macro that works ok, except that with each sheet deletion, I
get a pop-up that stops the macro execution until I manually acknowledge it.
Oh, another annoyance is that if the sheet does not exist, the macro
crashes.......

Could anyone please help with either problem?


Sub ClearProgram()

'Delete the unwanted sheets
Sheets("q793complete").Select
ActiveWindow.SelectedSheets.Delete
Sheets("tblDPM").Select
ActiveWindow.SelectedSheets.Delete
Sheets("PartTrend").Select
ActiveWindow.SelectedSheets.Delete
Sheets("q794Trends").Select
ActiveWindow.SelectedSheets.Delete
Sheets("q261Defects").Select
ActiveWindow.SelectedSheets.Delete
Sheets("q261Parts").Select
ActiveWindow.SelectedSheets.Delete
'Clear the NUMBERBASE range on the DPMcalcs sheet
Sheets("DPMCalcs").Select
ActiveWindow.ScrollColumn = 1
Application.GoTo Reference:="NUMBERBASE"
Selection.ClearContents
Sheets("Main").Select
Range("C8").Select

End Sub

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

CLR,
You need to turn the warnings off.
Try...
Application.DisplayAlerts = False
YOUR CODE HERE
Application.DisplayAlerts = True

HTH,
Gary Brown

 

[Guest]

Thanks very much Gary.......That took care of the first part, real fine.....


Vaya con Dios,
Chuck, CABGx3

 

[Guest]

at the top of the code add

on error resume next

When the sheet to delete is not found the code will just continue on the
next line...

HTH

 

[Guest]

Hmmmm.....I must be doing something wrong......when I did that, after adding
Gary's suggestion, the macro passed over that part of the code where it
looked for the sheets I wanted to delete.........but then for some reason, it
wiped out almost every other sheet in the book.

Vaya con Dios,
Chuck, CABGx3

 

[Sergio Mendes]

Here is a solution to your problem:

' Delete Summary sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary").Delete
On Error GoTo 0

The "Application.DisplayAlerts = False" will avoid the pop-
up. The "On Error Reume Next" will avoid the error in case
the sheet does not exist.

Sergio Mendes

 

[Guest]

Sorry... Wee need to clean up some of your delet code...

Change all of the deletes as follows:

sheets("SheetName").delete

instead of
sheets("Sheetname").select
activewindow.selectedsheets.delete

My goof.

 

[Guest]

When it can't find the worksheet, it goes to the next line which basically
says DELETE THE CURRENT WORKSHEET.
Use this syntax..
Sheets("q793complete").Delete
That way, if it can't find the sheet, it moves on instead of doing a blind
delete.
HTH,
Gary Brown

 

[Guest]

Thank you all......Gary, Jim, and Sergio.......with you guys help, it all
works super-fine now.....

Thanks again
Vaya con Dios,
Chuck, CABGx3