ExcelBanter - recording macro to delete sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - recording macro to delete sheet (https://www.excelbanter.com/excel-programming/298036-recording-macro-delete-sheet.html)

recording macro to delete sheet

I was recording a macro to delete one of the worksheets in my Excel file
because I create a new one each time for the task at hand. When I "turned
on" the recording and carried out the steps, I clicked OK to the prompt to
delete the sheet. It doesn't appear to "record" clicking OK because when I
ran the macro later, I had to click OK on that prompt.

Sheets("filtered").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Complete List").Select

I am creating this macro to automate some tasks and someone else will be
running it. My goal is to make it user-friendly so the person will not have
to "intervene" and couldn't possibly mess up the steps. I do okay recording
macros but am not very experienced with the VBA language. Is there anyway
to modify the code to delete the sheet without having the user click OK?

Thanks,
Marcia

recording macro to delete sheet

Marcia,

if you enclose the code you have recorded in the lines:

Application.ScreenUpdating = False
....
Application.ScreenUpdating = True

the message will be suppressed. One thing to note,
however. Your code will only work if the sheet you've
created is called "filtered". That's fine if your user
will now to call it that, but if you want it to work on
any sheet, you could amend it to:

Application.ScreenUpdating = False
ActiveSheet.Delete
Sheets("Complete List").Select
Application.ScreenUpdating = True

Then again, the user could just right click the sheet tab
and choose Delete, which would be less effort all round...

Cheers, Pete.

-----Original Message-----
I was recording a macro to delete one of the worksheets
in my Excel file
because I create a new one each time for the task at
hand. When I "turned
on" the recording and carried out the steps, I clicked OK
to the prompt to
delete the sheet. It doesn't appear to "record" clicking
OK because when I
ran the macro later, I had to click OK on that prompt.

Sheets("filtered").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Complete List").Select

I am creating this macro to automate some tasks and
someone else will be
running it. My goal is to make it user-friendly so the
person will not have
to "intervene" and couldn't possibly mess up the steps.
I do okay recording
macros but am not very experienced with the VBA
language. Is there anyway
to modify the code to delete the sheet without having the
user click OK?

Thanks,
Marcia

.

recording macro to delete sheet

Thanks for the help.
Marcia

"Kevin McCartney" wrote in message
...
Hi

You need to turn off the application alerts so just but before and after
the following:

Application.DisplayAlerts = False
Your Code
Application.DisplayAlerts = True

ciao
KM

----- Marcia wrote: -----

I was recording a macro to delete one of the worksheets in my Excel
file
because I create a new one each time for the task at hand. When I
"turned
on" the recording and carried out the steps, I clicked OK to the
prompt to
delete the sheet. It doesn't appear to "record" clicking OK because
when I
ran the macro later, I had to click OK on that prompt.

Sheets("filtered").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Complete List").Select

I am creating this macro to automate some tasks and someone else will
be
running it. My goal is to make it user-friendly so the person will
not have
to "intervene" and couldn't possibly mess up the steps. I do okay
recording
macros but am not very experienced with the VBA language. Is there
anyway
to modify the code to delete the sheet without having the user click
OK?

Thanks,
Marcia

recording macro to delete sheet