O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  



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
[Discuss (0) | Link to this hack]

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

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


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.