Workbook Change Macro

[ron2k]

Hi guys,

I have a workbook with about 20 worksheets. Three of them are summaries of all the other worksheets of the workbook. On each cells B1,B2 and B3 of each of those three worksheets I have the same drop down validation lists (meaning list on B1 of ws1 is the same as list on B1 or ws2 and ws3). What I want is that if I make a change to any of the drop down lists (on B1:B3) on any ws that the same change is reflected on the other two.

I came up with this, but I don't seem to make it work the way it should:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rng As Range
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, wss As Worksheet
    
    On Error GoTo errExit
    Application.EnableEvents = False
    
    Set rng = ActiveSheet.Range("B1:B3")
    Set ws1 = Sheets("Comparison Detail")
    Set ws2 = Sheets("Comparison Summ")
    Set ws3 = Sheets("Stat of Inc")
    Set wss = Union(ws1, ws2, ws3)
    
    If Intersect(Target, Range("B1:B3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each ws In wss.Worksheets
    If ws.Name <> Sh.Name Then ws.Range("B1:B3").Value = Target.Value
Next ws

errExit:
    Err.Clear
    On Error GoTo 0
    Set rng = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
    Set wss = Nothing
    Application.EnableEvents = True
End Sub

Suggestions on what to change to do what I want it to do?

Thanks,

Ron

 

[mikerickson]

Perhaps

[Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
    Case "Comparison Detail", "Comparison Summ" ,"Stat of Inc"
        On Error Goto ErrorOut
        Application.EnableEvents = False
        Sheets("Comparison Detail").Range("B1:B3").Value = sh.Range("B1:B3").Value
        Sheets("Comparison Summ").Range("B1:B3").Value = sh.Range("B1:B3").Value
        Sheets("Stat of Inc").Range("B1:B3").Value = sh.Range("B1:B3").Value
End Select
ErrorOut:
    On Error Goto 0
    Application.EnableEvents = True
End Sub

 

[ron2k]

Yep, that did!

Nice!

Thanks,

Ron