Delete A Sheet

You can delete or remove any sheet from a workbook as long as you have the ability to modify it. If you open the workbook in read-only mode or if another user has protected the workbook, you cannot make any modifications to the workbook, including the removal of sheets.

You can delete a sheet using the Delete method with the Sheets object. This combination enables you to remove any type of sheet from the workbook, including worksheets, chart sheets, and macro sheets. To use this method, you must identify the sheet you want to remove, as illustrated in the following code, which removes the first worksheet in the workbook: Sheets(1).Delete.

Although Excel numbers sheets and charts as you add them to the workbook (for example, Sheet1, Sheet2, or Chart1, Chart2, and so on), it does not necessarily reference sheets in numeric order. If you use a numeric index value to specify the first sheet in a workbook, Excel considers the first sheet to be the one with the tab in the bottom left corner. If you move sheets within the workbook, Excel reorders them within the Sheets object.

You can also reference the sheet you want to delete using the sheet name. If you specify a sheet name, you must enclose the name of the sheet in double quotes, for example: Sheets( "Sheet3 " ).Delete.

No matter what method you use, Excel displays a message box to verify that you really want to remove the sheet. You remove the specified sheet from the workbook using the Delete button. Remember, if the sheet contains any data, Excel permanently removes all data as well as the specified sheet.

DELETE A SHEET

DELETE A SHEET

LQ Create a new subroutine.

Type Dim DeleteWS As String, replacing DeleteWS with the variable name of the sheet to delete.

LQ Create a new subroutine.

Type Dim DeleteWS As String, replacing DeleteWS with the variable name of the sheet to delete.

< Assign the name of the sheet to remove from the workbook to the DeleteWS variable.

■ This example uses the InputBox function to request the sheet name from the user.

0 0

Post a comment