move sheet without names ranges

[Hammer_757]

I create weekly reports using Data validation drop down menus in several columns. The data is located via named ranges.
Once complete I run a macro that makes a new "clean" sheet with just the values and any local named ranges removed.

Please Login or Register  to view this content.

After that I right click on the sheet tab and move the "clean sheet" to a separate summary workbook that I email to the recipients.

The problem is that the global named ranges from the source workbook move with the clean sheet.

to remove the names i can run this code in the destination workbook:

Please Login or Register  to view this content.

In order to avoid security issues I then have to copy the "clean" and "name free" sheet to a third workbook that is "macro free"
That works but I would like to get rid of the extra step.

What I am thinking is to run "moving the worksheet" via a macro and either clean the global names after the move as part of the macro or if possible instruct excel to move the sheet without the named ranges.
Any advice on which path to take or another option?

thanks
Robert

[wallyeye]

You should be able to move the sheet to the new workbook via VBA, then clean out the names.

Please Login or Register  to view this content.

[Hammer_757]

Wallyeye, thanks your reply and code suggestion.
I'm receiving a "Subscript out of range (Error 9)" at the line:

Please Login or Register  to view this content.

So far I am unable to resolve it.
any suggestions?

[Hammer_757]

Figured it out. I just needed to get the focus back on the source workbook. I added the line:

Please Login or Register  to view this content.

ahead of the copy line.
Thanks again for the help Wallyeye

[wallyeye]

You could preface the copy:

Please Login or Register  to view this content.

rather than activating the sheet. It will give you a very slight performance increase.