Hack 2 Enter Data into Multiple Worksheets Simultaneously

   

Hack 2 Enter Data into Multiple Worksheets Simultaneously

It's fairly ordinary to have the same data appear in multiple worksheets simultaneously. You can use Excel's tool for grouping so that data in one workbook can be entered into multiple worksheets at the same time. We also have a quicker and more flexible approach that uses a couple of lines of Visual Basic for Applications (VBA) code .

Excel's built-in mechanism for making data go to multiple places at once is a feature called Group . It works by grouping the worksheets together so that they're all linked within the workbook.

Grouping Worksheets Manually

To use the Group feature manually, simply click the sheet into which you will be entering the data, and press the Ctrl key (the Shift key on the Macintosh) while clicking the Name tabs of the worksheets where you want the data to go. When you enter data into any cells on your worksheet, they will be entered automatically in the other grouped worksheets. Mission accomplished.

To ungroup your worksheets, either select one worksheet that is not part of the group or right-click any Name tab and select Ungroup Sheets.

When your worksheets are grouped together, you can look up to the titlebar and you will see the word Group in square brackets. This lets you know your worksheets are still grouped. Unless you have eagle eyes and a mind like a steel trap, however, it is highly likely that you won't notice this or you'll forget you have your worksheets grouped. For this reason, we gently suggest you ungroup your sheets as soon as you finish doing what you need to do.

Although this method is easy, it means you need to remember to group and ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. It also means simultaneous data entries will occur regardless of the cell you are in at the time. For example, you might want the simultaneous entries to occur only when you are in a particular range of cells.

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 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 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, see [Hack #39]) Change MyRange to the range name you are using on your worksheet. Also change the three sheet names in the code, as shown in Figure 1-5, to the sheet names you want to be grouped. When you're done, either close the module window or press Alt/ -Q to get back to Excel.

Figure 1-5. 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 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. The beauty of this hack is that there is no need to manually group the sheets and therefore run the risk of forgetting to ungroup them. This approach can save lots of time and frustration.

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("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. Follow the steps described earlier in this hack to get it there.