Add A Sheet

You can add a new sheet to a workbook using the Add method with the Sheets object. You use this method to add any type of sheet to a workbook, worksheet, chart sheet, or macro sheet.

The Add method has four optional parameters that specify where in the workbook to place the sheet, the number of sheets to add, and the type of sheet to create:

ThisWorkbook.Sheets.Add(Before, After, Count, Type).

You use the Before parameter, the parameter Excel applies when you do not specify any parameters, to place the sheet before the currently active sheet in the workbook. You use the After parameter to place a worksheet after the active sheet. You reference a sheet either by the sheet name or using the Worksheets Collection with an index value, as in the example: ThisWorkbook.Sheets.Add Before:=Worksheets(1).

Excel references sheets within a Worksheets Collection based on the order of the sheets within the workbook from right to left, with the worksheet on the left being the first sheet with an index of Worksheet(1).

You can add any number of sheets to a workbook at one time using the Count parameter. If you do not specify a value for the Count parameter, Excel adds only one sheet to the workbook.

By default the Add method creates an Excel worksheet when it is called. You can also use this method to create chart or macro sheets. You specify the type of sheet you want to create using one of the four XLSheetType constant values. If you specify xlWorksheet, Excel adds a new worksheet. Use xlChart to create a new chart. If you want to create a macro sheet you can use xlExcel4MacroSheet. Use xlExcel4IntMacroSheet to create an international macro sheet.

ADD A SHEET

ADD A SHEET

—n Create a new subroutine.

-0 Type ThisWorkbook.Sheets.Add.

—n Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

-0 Type ThisWorkbook.Sheets.Add.

0 0

Post a comment