Making range names "local"

[Ed S]

I want to set up the same range names on multiple sheets
in one workbook. That way I can run a common macro from
each sheet, but use the "local" sheet's range
definitions. I'm sure this can be done, because the
print_area range is unique to each sheet.

Thanks!

 

[Dave Peterson]

Include the sheetname in your name:

Sheet1!myName

Would go into the Names in Workbook box in the Insert|Name|Define dialog.

Now, Go get a copy of 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

It makes working with names much easier.

It even has an option to globalize (or localize) existing names.

 

[BrianB]

You can use the same name in different worksheets. Not sure exactly, but
think that you can do what you want by explicitly stating the range as
shown below.

When range names are first created manually they are added to the
WorkBook.Names collection, if the same name is used elsewhere the name
goes to WorkSheet level into the .Names collection for that sheet.

eg. If MyRange is at WorkBook level the following line will work, no
matter which sheet is active :-

x = Range("MyRange").Value

If the ActiveSheet contains the same name, it will use the range
there.
If the ActiveSheet does *not* contain the name it will use the
WorkBook level one - or crash if it does not exist at this level.

To avoid problems I always refer to range names explicitly in code like
:-

x = Worksheets("Sheet1").Range("MyRange").Value
x = Worksheets("Sheet2").Range("MyRange").Value
x = ActiveSheet.Range("MyRange").Value
etc.