How to simplify; dozens of Excel Tabs with the same underlying VBA Code

Question

I have 51 unique tabs in a workbook. Each tab has a bit of code that will update the 52nd tab when certain cells are changed on the 51. Bottom line, it's an audit history of the 2 cells on each of the 51 tabs.

I've pieced together the following code that I drop onto each worsheets VBA section. The problem is that I have to do this for every single sheet in the workbook. I'd think that I should be able to have just a single common call to the meat of the VBA...

Dim PreVal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$D$1" Or Target.Address = "$D$2" Then
    PreVal = Target.Value
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$1" Then
    If Target.Value <> PreVal Then
        SomethingSomewhere = Value
        PreVal = Target.Value
    End If
End If

If Target.Address = "$D$2" Then
    If Target.Value <> PreVal Then
        SomethingSomewhere = Value
        PreVal = Target.Value
    End If
End If

End Sub

It works wonderfully, just managing any changes needs to be done on every single sheet..

BTW, the SomethingSomewhere equals Value sets the app user name, sheet name, preval, target value, and date time into columns on the logging page

Answer 1

Create a subroutine that contains the logic and then create the application you drop on to each sheet. This application will call the subroutine. Now, when you change the subroutine, all of the sheets will pickup the same modification.