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