Advice on updating templates

bolony21

New Member
Joined
Jul 22, 2016
Messages
40
Hi,

I have developed a range of Excel tools which are combined together into a core sheet periodically.

Multiple users input into the child templates and there can be 50+ of these live at any one time.

The child templates must all be exactly the same so that when the combination takes place it does not throw up errors.

------------------------------------------------------------------------

My question is around the updating/maintenance of these child templates.

Currently if I update a function/find a bug/make improvements, I need to spend hours over the weekend to individually enter into each live child template and update manually.

Obviously, this is not ideal and was looking for some advice on how you would accomplish this task. Am I overlooking a very simple way of making changes en-mass to multiple workbooks?

Thanks in advance.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,257
Office Version
365
Platform
Windows
Hi Bolony,
depending on what update you're making you could try to script your update with VBA. So say you'd need to update the templates by inserting a row below row 5 and changing the formula in D16 to =A1, you could run a script that loops through the template files, opens them, makes those changes and saves the files again. In order to do that more efficiently:
  • in every template file, add a named range with the version number (not in a sheet, just go to names to define e.g. "version_number" as =2020022401 ->date + 2 digit number), which you can update when you've updated it.
  • test your fixes/improvements on one local copy of the current version of the template file
  • if that works, run a loop for all the files
The tricky bit is making the code for the update, but after that you're at least sure that you haven't made any manual mistakes. Warning: do test your code before you deploy, macros are also a great way to mass-mess-up your sheets :).
Cheers,
Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top