Grouping Sheets

Worksheets can be manually grouped by holding down the Shift (or Ctrl) key as you click on several sheets.
It is possible to group sheets by using the Select method of the Worksheets collection in conjunction with the Array function.
The following code groups the first, second and third sheets of a workbook and makes the second worksheet active.

Worksheets( Array(1,2,3) ).Select 
Worksheets(2).Activate 

It is also possible to group particular worksheets using the Select method of the worksheet object.
The first worksheet is selected in the normal way and subsequent worksheets are added to the group by using the Select method while setting its Replace parameter to False.

Worksheets("Sheet1").Select 
Worksheets("Sheet1").Select Replace:=False 
Worksheets("Sheet1").Select Replace:=False 

This technique can be useful when you want to group specific sheets.

Making Changes

When you group sheets manually any changes or formatting changes made to one sheet are made to the all.
This is not the case when you apply changes to a group using VBA.
Only the active sheet is affected when you apply changes to a grouped sheet using VBA code.
The only way to make changes to all the worksheets in a group is to use a For-Each loop and apply the changes to each worksheet individually.

Set colSheets = Worksheets( Array(1,2,3) ) 
For Each objWorksheet in colSheets 

Next objWorksheet 

Currently Selected Sheets

If you want to identify the sheets that are currently grouped you can use the SelectedSheets property of the Window object.

For Each objWorksheet in ActiveWindow.SelectedSheets 

Next objWorksheet 

This property is a member of the Window object because you can open several windows of the same workbook and each window can display a different worksheet (or worksheet group).

Adding the following code will mean that any values enters into cells in the named range "NamedRange" on Sheet1 will automatically also appear in Sheet2 and Sheet3 automatically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
   If Not Intersect(Range("NamedRange"), Target) Is Nothing Then 
      Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select 
   Else 
      Me.Select 
   End If 
End Sub 

If you want the same data to appear on other sheets but in a different place use the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
   If Not Intersect(Range("NamedRange"), Target) Is Nothing Then 
      Range("NamedRange").Copy Destination:=Sheets("Sheet2").Range("A1") 
      Range("NamedRange").Copy Destination:=Sheets("Sheet3").Range("C20") 
   Else 
      Me.Select 
   End If 
End Sub