Named ranges move out with worksheet

[kualjo]

I have a worksheet with a report on it that populates based on information in other worksheets. When it's ready I do a Move/Copy to a new workbook and check Create a copy. Once the new file is created, I select the entire worksheet, copy, and paste values to be rid of all the formulas, which are no longer needed. For some reason, though, about 30 named ranges from the source file come along for the ride. The source file actually has around 100 named ranges, so it's strange that they don't all come over. I noticed that some of them are related to the fields that I hard coded, but others are not. Doesn't seem to be any rhyme or reason to what it's doing. I don't need the old formulas anymore, and I don't need the named ranges scoped to the source workbook either. How can I move a worksheet to a new book and leave all the named ranges behind?

BTW, I can't change the scope of any of the ranges to the Worksheet level because they are used on other worksheets in the source file. I have to leave them at the Workbook level.

 

[rlv01]

A couple of ways I can think of.

1. After you move/copy your sheets to a new workbook, use vba to iterate through the workbook and worksheet level names in the new workbook and delete them.
2. Don't copy/move existing worksheets to a new workbook. Instead, use vba to create a new workbook and then copy/paste the values and/or formats.