Categories
Excel

Using VBA to Group and Ungroup Worksheets

Although manually grouping and ungrouping sheets is easy, you must remember to group and ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. 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 View Code.

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.

The first thing to do to make grouping work is to name the range of cells you want to have grouped so that the data shows automatically on other worksheets. Enter this code into the VBA code editor:

Example: Group Worksheets If Specific Cell Range Selected

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

In this code, we used "A1:D10" range, you can use the named range instead. Change the range "A1:D10" to the range you are using on your worksheet. Also, change the sheet names in the code to the sheet names you want to be grouped. When you’re done, either close the module window or press Alt+Q (or ⌘+Q) to get back to the worksheet.

Figure. Code for automatically grouping worksheets

Group worksheets when a specific range

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 "A1:D10". If it is, the code will automatically group the worksheets you want to be grouped. If it isn’t, it will ungroup the sheets simply by activating the sheet you are already on.

Enter Data into Multiple Worksheets Simultaneously without Grouping Sheets via VBA

If you want the same data to appear on other sheets but not in the same cell addresses, code like this:

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

This code uses the Worksheet_Change event handler. When a cell in the worksheet is changed, it checks the cell range, if matched if copies the data to the Sheet1 and Sheet3 to the G11:J20 range.

This code also needs to live within the private module of the Sheet object. Follow the steps described earlier in this tutorial to get it there.

To learn more about the events handler used in this tutorial, visit: Understand Worksheet_Change and SelectionChange Events.


Understanding Workbooks and Worksheets: