0

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

  1. 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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Gee
  • 59
  • 2
  • 9
  • 2
    There are some ways to trigger events when any sheet in the workbook is changed. Would this be appropriate? If so, the code would only have to exist at the workbook level. – JG7 Dec 21 '17 at 02:15
  • Look up what an XLAM (Excel Add-On) file is and does. But if you expect changes to be made when a cell's content changes, you will have to use the worksheet_change event. Perhaps if you posted some of the code someone would be able to see if any of it could be optimized. – braX Dec 21 '17 at 02:20
  • As per what @JoeyGrant suggested, instead of having a change event code for each sheet in the sheet module, all you need is to place only one change event code on `ThisWorkbook Module` and that will work for all the sheets in the workbook. Of course you will need to delete the change event codes from individual sheet modules. – Subodh Tiwari sktneer Dec 21 '17 at 02:50
  • You should be able to add macros into a workbook via VBA, however it requires some Excel Trust Center changes on both yours and colleagues. So an AddIn is much preferred. For auto add-in update, you may need to use batch file to copy the later version into the User's profile. BUT, really depends on relation between this master file and actions to take, you may end up splitting the macro to an AddIn and a Macro Enabled Template. This is designing which takes time! – PatricK Dec 21 '17 at 04:29

1 Answers1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73