Thanks Allllen, very useful references. Unfortunately due to my current level
of skill I wasn't able to make very much progress with either solution :-(
Copying the workbook worked fine in terms of getting the macro across but I
lost context on the master sheet so I couldn't work my way down the cell
range I was using to drive the copies.  Anyway I took a look at the
information in the link and it made me rethink what I am actually trying to
achieve and whether there is a simpler, more streamlined approach.

With hindsight, if I can hold the macro in a separate file and copy it in to
the generated sheet then I will achieve my objective and simplify the master
sheet (where the initial macro is not strictly necessary). However when I
copy the exported macro into the generated sheet (from the *.cls file, using
ActiveWorkbook.VBProject.VBComponents.Import) it stores it as a separate
'class' object and it is not run when the workbook opens. Is there a simple
way round this or am I barking up the wrong tree?

Thanks again