VBA Code - Can someone give me an explination of the code included?

[turtle_solver]

I would like to get more knowledgable on the code I am using. Would someone be able to go through the code posted below?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

    Dim arrayOfNames As Variant
    Dim rangeChanged As Range, oneArea As Range

    Select Case False

        Case (Application.Intersect(Target, Sh.Range("C3")) Is Nothing)
            arrayOfNames = Array("Overall", "Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("C3"))
        Case (Application.Intersect(Target, Sh.Range("C5")) Is Nothing)
            arrayOfNames = Array("Overall", "Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("C5"))
        Case (Application.Intersect(Target, Sh.Range("F3")) Is Nothing)
            arrayOfNames = Array("Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("F3"))

    End Select

    On Error GoTo ResetEvents

    If IsNumeric(Application.Match(Sh.Name, arrayOfNames, 0)) Then
    
        If Not rangeChanged Is Nothing Then

            Application.EnableEvents = False

            For Each oneArea In rangeChanged.Areas
                Sheets(arrayOfNames).FillAcrossSheets oneArea
            Next oneArea   

        End If

    End If

ResetEvents:
    Application.EnableEvents = True
    On Error GoTo 0

End Sub

 

[tweedle]

This is a rather elegant method of synchronizing multiple sheets.
Even when cells are changed by pasting or dragging values.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
[COLOR=seagreen]'its using Workbook_SheetChange so anytime anything is changed in the workbook, the event is fired and the tests performed. [/COLOR]
    Dim arrayOfNames As Variant 'this is allocating an array to hold sheet names
    Dim rangeChanged As Range, oneArea As Range ' these are allocating ranges
 
 
    Select Case False
[COLOR=seagreen]'Interesting use of select, but essentially testing if the change that just occurred occurs in any of the cells C3, C5 or F3[/COLOR]
[COLOR=seagreen]'If so then fill the array with sheet names used later with the [/COLOR]
[COLOR=seagreen]'FillAcrossSheets function[/COLOR]
[COLOR=seagreen]'Also set a range reference to the cell(s) that we care about and were detected as changed[/COLOR]
        Case (Application.Intersect(Target, Sh.Range("C3")) Is Nothing)
            arrayOfNames = Array("Overall", "Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("C3"))
        Case (Application.Intersect(Target, Sh.Range("C5")) Is Nothing)
            arrayOfNames = Array("Overall", "Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("C5"))
        Case (Application.Intersect(Target, Sh.Range("F3")) Is Nothing)
            arrayOfNames = Array("Sales", "Units", "Spend")
            Set rangeChanged = Application.Intersect(Target, Sh.Range("F3"))
    End Select
 
[COLOR=seagreen]'If something goes awry, get out with some grace[/COLOR]
    On Error GoTo ResetEvents
 
[COLOR=seagreen]'Now we are going to test for IsNumeric on the Match to other sheet cells[/COLOR]
[COLOR=seagreen]'If the sheet that changed is in the array, then continue with the update(s) [/COLOR]
    If IsNumeric(Application.Match(Sh.Name, arrayOfNames, 0)) Then
 
[COLOR=seagreen]      'The Cell(s) that changed need to be one of the three we're looking for (C3,C5,F3)[/COLOR]
        If Not rangeChanged Is Nothing Then
[COLOR=seagreen]           'Don't fire any other events with the updates we're about to make, doing so would cause a looping scenario back into this routine and eventually freeze/crash Excel[/COLOR]
            Application.EnableEvents = False
 
[COLOR=seagreen]           'For every cell in our target list (C3, C5, F3)[/COLOR]
[COLOR=seagreen]           'Replicate the value to the other sheets in the array of sheet names[/COLOR]
            For Each oneArea In rangeChanged.Areas
                Sheets(arrayOfNames).FillAcrossSheets oneArea
            Next oneArea
        End If
    End If
ResetEvents:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

HTH