Wiki - README‎ > ‎Programming‎ > ‎

Excel

Syncing cells (from link)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cel As Range
Set cel = Sh.[A2]   'Watch cell A2 on all worksheets for changes
If Intersect(Target, cel) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.Sheets.FillAcrossSheets cel
Application.EnableEvents = True
End Sub
and
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cel As Range
Dim ws As Worksheet
Dim wss As Sheets
Set cel = Sh.[A2]   'Watch cell A2 on all worksheets for changes
If Intersect(Target, cel) Is Nothing Then Exit Sub
For Each ws In Me.Worksheets
    Select Case ws.CodeName
    Case "Sheet2", "Sheet4" 'Don't include these worksheets in the synchronization
        If Sh.CodeName = ws.CodeName Then Exit Sub
    Case Else
        If ws.CodeName <> Sh.CodeName Then ws.Select Replace:=False    'Add worksheet to selected sheets 
    End Select
Next
Set wss = ActiveWindow.SelectedSheets
Application.EnableEvents = False
wss.FillAcrossSheets cel    'If cel is part of a merged cell range then use cel.MergeArea instead
Sh.Select Replace:= True    'Ungroup the worksheets
Application.EnableEvents = True
End Sub



Comments