Grouping Worksheets Automatically

You can overcome these shortcomings by using some very simple VBA code. For this code to work, it must reside within the private module for the Sheet object. To quickly go to the private module, right-click the Sheet Name tab and select ViewCode. You can then use one of Excel's sheet events-which are events that take place within your worksheet, such as changing a cell, selecting a range, activating, deactivating, and so on-to move the code into the private module for the Sheet object.

In most cases, you will be taken directly to the private module when you right-click on a workbook or worksheet and select ViewCode. You can confirm that you're in the private module by looking at the state of the drop-down menu in the upper left of the code window-this window is usually labeled something like "Workbook - sheetname (Code)". If the drop-down menu says "Workbook" or "Worksheet," then you in the private module. If it says "(General)," change it before typing in the code.

To enable grouping, first name the range of cells you want to have grouped so that the data shows automatically on other worksheets.

Enter this code into the private module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Range("MyRange"), Target) Is Nothing Then
     'Sheet5 has purposely been placed first as this will
     'be the active sheet we will work from
     Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
     Else
     Me.Select
     End If
    End Sub

In this code, we used the named range MyRange. (If you aren't familiar with named ranges) Change MyRange to the range name you are using on your worksheet. Also change the three sheet names in the code, to the sheet names you want to be grouped. When you're done, either click the View Microsoft Excel tool, close the module window, or press Alt+Q to get back to Excel, then save your workbook.

It is important to note that the first sheet name used in the array must be the sheet housing the code, and thus the worksheet on which you will enter the data.

Once the code is in place, each time you select any cell on the worksheet, the code checks to see whether the cell you selected (the target) is within the range named MyRange. If it is, the code will automatically group the worksheets you want grouped. If it isn't, it will ungroup the sheets simply by activating the sheet you are already on. If you want the same data to appear on other sheets but not in the same cell addresses, use code like this:

    Private Sub worksheet_Change(ByVal Target As Range)
     If Not Intersect(Range("MyRange"), Target) Is Nothing Then
     With Range("MyRange")
     .Copy Destination:=Sheets("Sheet3").Range("A1")
     .Copy Destination:=Sheets("Sheet1").Range("D10")
     End With
     End If
    End Sub

This code also needs to live within the private module of the Sheet object.

[Previous] [Contents]

Grouping Worksheets Automatically

In this tutorial: