Yes, we should be able to do something with this ...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const sChangeRange = "S4"
Const sGraphSheets = "Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)"
Const sTargetBook = "DAILYPLOT:Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)"
Const sTargetRange = "G20:S4:S4:S4"
Dim asUpdateSheets() As String
Dim asUpdateRanges() As String
Dim lUpdateLoop As Long
asUpdateSheets=Split(sTargetBook,":")
asUpdateRanges=Split(sTargetRange,":")
If InStr(sGraphSheets, Sh.Name) > 0 Then
If Not Intersect(Target, Sh.Range(sChangeRange)) Is Nothing Then
Application.EnableEvents=False
For lUpdateLoop=LBound(asUpdateSheets) To UBound(asUpdateSheets)
Sheets(asUpdateSheets(lUpdateLoop)).Range(sUpdateRanges(lUpdateLoop)).Value = Sh.Range(sChangeRange).Value
Next lUpdateLoop
Application.EnableEvents=True
End If
End If
End Sub
This will loop through however many sheets are specified in the sTargetBook constant and, for each one, update the correspoding cell in sTargetRange.
sTargetBook and sTargetRange must both contain the same number of elements, even if the same cells are to be updated in each sheet.
Does that make sense?
Bookmarks