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
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.