Move sheet or sheet content while preserving range names

[Carl Colijn]

During the last 2 weeks I've been trying to solve a seemingly unsolvable problem: how to set up a workbook with VBA, where the VBA needs to be signed, and still have users copy sheets in it, without that copying breaking the signature.

There seems to be a narrow escape route, but it's still not clear to me if there is light at the end of this very narrow tunnel, or if I'm already stuck beyond belief.

I've originally started 3 posts on separate aspects of this on StackOverflow, now encompassed by a final 4th, but they do not seem to get much traction there. Hopefully this community is not filled with the same gurus as there

All nitty gritty details are already in the above mentioned StackOverflow post, but as a quick summary: the reason is that I need to get rid of the VBA code modules behind the sheets of my VBA-filled (and signed) workbook. When they are present, copying (or deleting) these sheets by my customer will break the digital signature when saving the workbook again. So I cannot even hand over this workbook to my customer without it 'malfunctioning' at their end after the first try.

Unfortunately, just emptying it in the VBA IDE doesn't cut it. What does work is to either create brand new sheets and copy over the content (they unbelievably have no code module, as long as you keep the VBE IDE closed!), or move them out into a temp .xlsx and back again (having the same effect). Once you open the IDE again, the sheets all get an empty VBA module again, and thus the code modules get 'baked in' from my end. I therefore need a VBA code module scrubbing mechanism. And the final result needs to be identical to where we came from.

Complicating factors are that all sheets have sheet-scoped range names defined on them, they all use each other's names, and they all have tables on them as well. The tables hinder a mass move of the sheets in one go, and when I tried moving individual sheets or just the sheet content, all I ended up with was broken names

Hopefully someone here has an out-of-the-box moment and is able to solve this mess! I've left a lot of details out of this post to not again write a new trilogy here; please refer to the above linked post for more details, try-outs and conclusions.

Thanks in advance!