PDA

View Full Version : Solved: Duplicate cells across sheets upon update



belly0fdesir
01-30-2006, 03:15 PM
A question of mine, similar to this one was x-posted here (http://www.mrexcel.com/board2/viewtopic.php?t=190705&postdays=0&postorder=asc&&start=10).


I have a sheet called Summary and anytime there is a change in cells 'Summary!B6:B120', I want the change to be replicated in Jan:Dec!B7:B121. Also, any time there is a change in Summary!F6:F120 I want it to be replicated in Jan:Dec!M7:M121.

Column B is a list of names. So my goal is to have any name changed or added to the list in Summary appear in the list in Jan:Dec. Column F is department, which is also shown in Jan:Dec!M7:M121 and I would like it to react in the same manner.

Does this make sense to anyone? Does anyone know of a method I could use to accomplish my goal? Thank you to anyone who considers giving me help.

XLGibbs
01-30-2006, 03:20 PM
Are those the only instances and only requirements, if it changes that that cell goes to the corresponding area (but 1 row down)?

This doesn't sound to harsh...if no one else jumps in, I can zip this out later on...Unfortuanately, I have other business to attend to for a couple of hours...will check back to see if any one else got you what you needed...

belly0fdesir
01-30-2006, 03:26 PM
That is correct for Column B. Any change made in Column B of Summary should also occur in Jan:Dec Column B, one cell down.

Any change in Column F should change Jan:Dec Column M, one cell down. (and I guess 7 columns to the right).

mdmackillop
01-30-2006, 04:16 PM
Here's some code as in the attached example. You'll need to add the rest of the months.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sh, s
Dim Tgt As String
Sh = Array("Jan", "Feb", "Mar") 'etc.

If Not Intersect(Target, Range("B6:B120")) Is Nothing Then
Tgt = Target.Offset(1).Address
For Each s In Sh
Sheets(s).Range(Tgt) = Target
Next
End If

If Not Intersect(Target, Range("F6:F120")) Is Nothing Then
Tgt = Target.Offset(1, 7).Address
For Each s In Sh
Sheets(s).Range(Tgt) = Target
Next
End If
End Sub

belly0fdesir
01-31-2006, 05:40 PM
Worked perfectly. Thank you very much.