Task Five: Deleting the Worksheet

You imported the text file worksheet so that you could fill in the labels and add a column of dates before appending the data to the database. Once the data is safely appended, you don’t need the imported worksheet any more. You don’t want to delete the Chapter02 workbook file, because it contains the macros you will need next month. You want to delete only the active worksheet.

Create a Macro to Delete the Active Worksheet

In this section, you’ll create a macro that deletes the active sheet. This can be useful in many contexts. While you’re developing and testing the DeleteSheet macro, you can use any arbitrary sheet.

1. Activate an expendable worksheet, click the Record Macro button, type DeleteSheet as the macro name, and then click OK.

2. On the Home tab of the Ribbon, click the Delete arrow, click Delete Sheet, and then click Delete when asked to confirm.

3. Click the Stop Recording button to turn off the recorder.

4. Edit the DeleteSheet macro. The body of the macro contains only one statement:

   ActiveWindow.SelectedSheets.Delete 

   The statement refers to the “selected sheets of the active window” because it’s possible to select and delete multiple sheets at the same time. (Press and hold the Ctrl key as you click several sheet tabs to see how you can select multiple sheets. Then click an unselected sheet without using the Ctrl key to deselect the sheets.) Because you’re deleting only one sheet, you could change the statement to ActiveSheet.Delete if you wanted, but that isn’t necessary.

The only problem with this macro is that it asks for confirmation each time you run it. When the macro deletes the imported sheet as part of the larger project, you would prefer not to be prompted.

Make the Macro Operate Quietly

The Delete method does not have an optional argument that eliminates the confirmation prompt. You must add a new statement to turn off the warning.

1. In the DeleteSheet macro, insert a new line just before the one statement already in the body of the macro, and then enter this statement:

   Application.DisplayAlerts = False 

   DisplayAlerts is a property of the Excel application. When you set the value ofthis property to False, any confirmation prompts that you would normally see are treated as if you had selected the default answer. The DisplayAlerts setting lasts only until the macro finishes running, so you don’t need to set it back to True. You do, however, need to be careful to never run this macro when the active sheet is something you care about. Naturally, you should also be careful to save your work often and keep backup copies.

   Tip 

   The Auto List Members feature will help you type the words DisplayAlerts and False. When you select a word in the list, press the Tab key to finish entering the word into the statement.

2. Save the Chapter02 workbook.

3. Select an expendable worksheet, and run the DeleteSheet macro.