Named Ranges not available in every worksheet

[Guest]

I built a workbook by copy worksheets from several another
workbooks. I now have the situation where I have some
named ranges that only exist in the worksheet they started
in and even two named ranges of the same name pointing to
different places on different workheets.

Are there any rules about copying worksheets that include
named ranges and if there away to make all named ranges
global and available to every worksheet within a workbook.

Thank you for your help

 

[Bob Phillips]

You are experiencing two types of named ranges, worksheet and workbook. The
former applied to just the worksheet it is defined on, the latter applies to
the whole workbook. As you noticed, any number of worksheets can have a
worksheet name by the same name, all pointing to specific ranges on their
particular worksheet.

Worksheet named ranges are created by specifying the worksheet name before
the name when defining the range name such as Sheet1!myRange.

Copying worksheets takes the worksheet name with it.

You can't make worksheet range names global, you would need to delete the
worksheet names and re-create as workbook names.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.co.uk/mvp

You get lots of options and can see differences very easily. It's well worth
the download.

You can localize and globalize names using this, too.