Copy A Range To Multiple Sheets

You can copy a range of cells and place it in the same location on multiple sheets with the FillAcrossSheets method. When you use this method, Excel copies the specified cells to each worksheet you specify. You can copy everything in the range of cells, just the values in the cells, or only the formatting.

When you use this method, you call it by indicating the range of worksheets where Excel should copy the cells followed by the FillAcrossSheets method. The range of worksheets must exist within the current workbook. Also, you must specify all of the worksheets at once, such as the Worksheets collection object to copy to all worksheets in the workbook. If you specify individual sheets within the workbook, the worksheet containing the range must be part of the range. The method includes two different parameters, Range and Fill, as illustrated in the following line of code: Worksheets.FillAcrossSheets(Range, Fill)

The first parameter, Range, is required. The Range parameter must specify the range of cells to copy to the other worksheets. You can specify the range of cells using any valid range statement. See Chapter 11 for more information on specifying ranges.

The second parameter, Fill, is optional. You can use this parameter to indicate how the range should copy. The Fill parameter accepts any one of the three XlFillWith constant values. If you do not specify a Fill parameter value, Excel uses the default value of xlFillWithAll which instructs Excel to copy the entire contents of the range of cells, including the formatting. If you only want to copy the cell values use the xlFillWithContents constant value. This constant value instructs Excel to copy everything but the cell formatting. On the other hand, if you only want to copy the formatting of the range of cells, specify the constant value of xlFillWithFormats. When you do this, Excel ignores the entire contents of the cell and only copies and applies the formatting.

COPY A RANGE TO MULTIPLE SHEETS

COPY A RANGE TO MULTIPLE SHEETS

'-H Create a new subroutine.

0 Type Dim WS As Variant, replacing WS with the variable to contain worksheet range.

'-H Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

0 Type Dim WS As Variant, replacing WS with the variable to contain worksheet range.

Type WS = Array("Sheet1", "Sheet3", "Sheet5"), replacing "Sheetl", "Sheet3", "Sheet5" with the worksheets to receive the cell values.

0 0

Post a comment