create named range specific to worksheet

Suppose I have a named range "x" in cell A1 of worksheet Sheet1, and I
copy the contents of Sheet1 to Sheet2. When I then write a formula on
Sheet2, "x" still refers to Sheet1!A1, when I want it to refer to
Sheet2!A2. In other words, I want each sheet copied from Sheet1 to have
its own set of named ranges, defined on that sheet. Can this be done?
Thanks.

Worksheets("Sheet2").Range("A1").Name = "Sheet2!X"

creates a sheet level name of X
--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Suppose I have a named range "x" in cell A1 of worksheet Sheet1, and I
> copy the contents of Sheet1 to Sheet2. When I then write a formula on
> Sheet2, "x" still refers to Sheet1!A1, when I want it to refer to
> Sheet2!A2. In other words, I want each sheet copied from Sheet1 to have
> its own set of named ranges, defined on that sheet. Can this be done?
> Thanks.
>

Sub makename()
ActiveSheet.Range("a1").Name = ActiveSheet.Name & "X"
End Sub

This is universal for all sheets. Unfortunately for you it is not
possible to have two or more ranges with same name within one workbook.
You can solve this by replacing e.g. sheet1x by sheet2x in your
formulas afer copying.