Updating a macro to be identical across all worksheets (or making the code more global?)

Question

I have a workbook with a few dozen worksheets, which is growing. I might also end up with copies of this workbook pretty soon.

Each sheet is based on the same worksheet template, which includes a macro on its Worksheet_Change event for auto-calculations. As I improve the workbook and add capabilities, and sheets are added, it's taking longer and longer to copy-paste the updated macro to all sheets.

I'm wondering if there's a way to either:

1. Make another macro that copies the updated version of the macro from the template to all other worksheets, overwriting the old versions in the process

And/or

2. Move the code from worksheet_change to a more global place where I'd only need to update one version of it per workbook (which is fine and much better than updating 20+ worksheets manually across soon-to-be 3 workbooks...)

Solution #2 would be better because more elegant I think? But I have no clue if it's possible. Barring that I'd gladly take #1 for the time-saving aspect!

Thank you in advance.

Answer 1

If we are talking about one workbook with multiple worksheets, then an easy approach (which solves the updating issue) would be:

1. Add a Module and write a procedure containing the original change events code:

   Option Explicit
   
   Public Sub MyGlobalWorksheet_Change(ByVal Target As Range)
       ' here the code from your orignal Worksheet_Change.
       ' make sure you reference worksheets correctly
       ' the worksheet can eg be addressed like
       ' set ws = Target.Parent
   End Sub
   

2. So in your worksheets you only need to add a generic call like

   Option Explicit
   
   Private Sub Worksheet_Change(ByVal Target As Range)
       MyGlobalWorksheet_Change Target
   End Sub
   

   to call the global procedure. Therefore the Worksheet_Change event never needs to be changed, however you just need to add it once.

   Whenever you need to change something at the code you just need to change one procedure which is MyGlobalWorksheet_Change and it affects all your desired sheets at once (but only sheets you added the call to your global event).

Remember it is always a bad idea to copy the same code over and over again, because it is hard to maintain. Instead always use one procedure you call again and again.

---

Another way would be using the Workbook_SheetChange event within the ThisWorkbook scope. But this will affect any sheet within the workbook. The previous solution will only affect the workbooks you choose by adding a call.