Excel Hacks Ebook [Electronic resources] نسخه متنی

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.