Solved: Duplicate cells across sheets upon update

[belly0fdesir]

A question of mine, similar to this one was x-posted here.


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]

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]

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]

Here's some code as in the attached example. You'll need to add the rest of the months.

[VBA]
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

[/VBA]

[belly0fdesir]

Worked perfectly. Thank you very much.