I created a worksheet with named cells and ranges and used a macro to
organize data within the worksheet. I then copied the sheet several times.
The named cells and ranges are valid for each separate sheet and the macro
runs independently for each active sheet using the same control keys. I've
been trying to add another range name that would be common to each seperate
worksheet that would be referenced by the macro just as the original cells
and ranges are, but the name becomes specific to the last sheet that the
name was defined on. How do I define names that apply to each sheet
independently. If I can't resolve this problem I will have to develop a
seperate macro for each worksheet in the workbook.

Hi Malcolm,
Not sure i understand what you're trying to do.
Can you give an example of the end result?

Are you trying to create book-level or sheet-level named ranges?

- book level is, eg, MyName pointing to sheet1!A1 and everywhere you call
MyName in the book , it points to sheet1!A1

- sheet level. Eg: You can create a name, say MyName in sheet1 pointing to
Sheet1!A1. From sheet1, you call it using MyName, but from other sheets, it
is not directly 'visible' and to call it youu need to call it Sheet1!MyName.
The extension of that is:
- In sheet1, you can call MyName the cell A1
- In sheet2, you can call MyName the cell A1
Now, when using the names:
- in sheet1, =MyName returns Sheet1!MyName = sheet1!A1
- in sheet2, =MyName returns Sheet2!MyName = sheet2!A1
This allows you to use the same formula in all sheets (=MyName) but each
point to a different range (based on in which sheet the name is defined)

Manually set a sheet-level name:
- Select Sheet1. Menu Insert Name Define.
- for Name, enter: Sheet1!Data
- for RefersTo, enter: =Sheet1!A1
- click Add, click OK
- Select Sheet2. Menu Insert Name Define
- for Name , enter: Sheet2!Data
- for RefersTo, enter: =Sheey2!A1
- click Add , click OK
- Now in sheet1!A1 and in Sheet2!A2 enter 2 different values and somewhere
else in both sheets , enter =Data.

Same programmatically:
ActiveWorkbook.Names.Add Name:="sheet2!Data", RefersTo:="=Sheet2!$A$1"
ActiveWorkbook.Names.Add Name:="sheet1!Data", RefersTo:="=Sheet1!$A$1"

Regards,
Sebastien


"Malcolm" wrote:

I created a worksheet with named cells and ranges and used a macro to
organize data within the worksheet. I then copied the sheet several times.
The named cells and ranges are valid for each separate sheet and the macro
runs independently for each active sheet using the same control keys. I've
been trying to add another range name that would be common to each seperate
worksheet that would be referenced by the macro just as the original cells
and ranges are, but the name becomes specific to the last sheet that the
name was defined on. How do I define names that apply to each sheet
independently. If I can't resolve this problem I will have to develop a
seperate macro for each worksheet in the workbook.