Deleting all named ranges that have a workbook scope

[Babymech]

Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my collection,
that range becomes global across the entire workbook. When I import the next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names, the
option to change it from Workbook to Worksheet is grayed out, and I have no
idea why...

Thanks a lot for the help

 

[Charles Williams]

Try using my Name Manager addin (developed by myself and Jan Karel Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Babymech]

Excellent - thanks. Your Name Manager worked wonders for converting the scope
of the names in the survey, so now I have a version with only local ranges.
However, I still have a number of survey files floating around with the old
setup, ie a bunch of Workbook-ranges... do you know a way to either convert
them by macro, or to use Name Manager to batch change the files I have?

Again - thanks for the tip and the excellent utility.

 

[Charles Williams]

Don't know of a batch method, what I would do is:
- start Excel
- open all the survey files
- start Name Manager
- set the right Name Manager Filter
- convert the active workbook, save it and close it
- repeat for the next active workbook

Since Name Manager automatically refreshes for the active workbook it should
be quite fast.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com