Working With Worksheets

If you want to create a subroutine that only removes worksheets from the workbook, you can use the Delete method with the Worksheets object instead of the Sheets object. The Sheets object contains all worksheets, chart sheets, and macro sheets open within a workbook, whereas the Worksheets object only keeps track of the open worksheets. If you use the Worksheets object to remove the first worksheet in the workbook, Excel ignores any chart sheets that exist in the workbook before the first worksheet. The following statement finds the first worksheet and ignores anything else that is not a worksheet.

Example:

Worksheets(1).Delete

On the contrary, if you only want to create a subroutine that removes chart sheets from the workbook, you can use the Delete method with the Charts object. The Charts object contains all of the chart sheets contained within the workbook. Keep in mind that this method only works with chart sheets, not charts embedded in worksheets. When you use the Charts object with the Delete method, Excel only considers actual chart sheets, and ignores any worksheets within the workbook, even if they exist before the specified chart sheet. The following code statement deletes the first chart sheet within the workbook, and ignores any other sheet types.

Example:

Charts(1).Delete

Working Sheets Introduction

L-Q Type Sheets(DeleteWS).Delete.

0 Switch to Excel and run the macro.

■ Excel removes the specified sheet from the workbook.

L-Q Type Sheets(DeleteWS).Delete.

0 Switch to Excel and run the macro.

Note: See Chapter 1 for more on running a macro.

■ Excel removes the specified sheet from the workbook.

0 0

Post a comment