how do I get past a "delete, cancel" button during a macro loop?

[curbster]

I would choose to select the delete button. I've created a new worksheet within a workbook, insert text and formatting, and copied to a specific place in the active worksheet. Everything works well except 1. there is a LOT of coding because I recorded the macro and 2. the delete message pops up for every files the macro manipulates. I have to click "delete" about 60 times throughout this macro and would love to be able to let it click its own delete button!

I'll attach an example.

Thanks.

[Leith Ross]

Hello Curbster,

The statement Application.DisplayAlerts will select the default button automatically for a dialog when set to false. This prevents you from ever seeing the dialog. When deleting a worksheet, the default button is Delete. Here is the worksheet deletion routine with the added code...

Please Login or Register  to view this content.

[curbster]

Worked like a charm! thank you!

I don't get prompted anymore and it glides on through.

[JBeaucaire]

Curbster, take a gander through this slimmed down version of your code. It appears you recorded yourself doing thing multiple times, which lengthened the code a bit. Also, the recorder records everything you do including selecting cells, something a macro doesn't usually need to do.

Compare this to your original code and you'll see how you can edit out a lot of unnecessary code that also gets recorded along the way.

FYI.

Please Login or Register  to view this content.

[JBeaucaire]

Deleted, see below.

[JBeaucaire]

Actually, it gets even shorter, this is about half the size of the original macro:

Please Login or Register  to view this content.

[Leith Ross]

Hello JB,

Your input is appreciated. Please follow the forum rules and don't post when the OP has marked the thread solved.