Grouping Worksheets

You can manually group the sheets in a workbook by clicking a sheet tab, then holding down Shift or Ctrl and clicking on another sheet tab. Shift groups all the sheets between the two tabs. Ctrl adds just the new sheet to the group. You can also group sheets in VBA by using the Select method of the Worksheets collection in conjunction with the Array function. The following code groups the first, third, and fifth worksheets and makes the third worksheet active:

Worksheets(Array(1, 3, 5)

.Select

Worksheets(3).Activate

In addition to this, you can also create a group using the Select method of the Worksheet object. The first sheet is selected in the normal way. Other worksheets are added to the group by using the Select method while setting its Replace parameter to False:

Sub GroupSheets()

Dim asNames(1 To 3) As Dim i As Integer

String

asNames(1) = "Jan 2007' asNames(2) = "Mar 2007' asNames(3) = "May 2007'

Worksheets(asNames(1)).

Worksheets(asNames(i; Next i

i).Select Replace:=False

End Sub

This technique is particularly useful when the names have been specified by user input, via a multi-select list box, for example.

One benefit of grouping sheets manually is that any data inserted into the active sheet and any formatting applied to the active sheet is automatically copied to the other sheets in the group. However, only the active sheet is affected when you apply changes to a grouped sheet using VBA code. If you want to change the other members of the group, you need to set up a For Each...Next loop and carry out the changes on each member.

The following code places the value 10 0 into the A1 cell of worksheets with index numbers 1, 3, and 5 and bolds the numbers:

Sub FormatGroup() Dim shts As Sheets Dim wks As Worksheet

For Each wks In shts wks.Range("A1").Value = 100 wks.Range("A1").Font.Bold = True Next wks

End Sub

Was this article helpful?

0 0

Post a comment