Move sheet or sheet content while preserving range names

Carl Colijn

New Member
Joined
Jun 23, 2011
Messages
32
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,171,893
Messages
5,878,068
Members
433,315
Latest member
KXZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top