Names can be either workbook-level or sheet-level.

When you create the sheet-level name (either in the Name box on the
formula bar or using Insert/Name/Define), prefix the name with the name
of the sheet, e.g.:

Sheet1!MyName

Sheet2!MyName


Note that if you use the name box, you'll need to delete the
workbook-level name first.

In article ,
Satyapal Kaushal <Satyapal wrote:

I would like to assign same name to two different ranges in in two different
sheets of same workbook. When we make a copy of sheet, EXCEL create such
names. But if we try to create from the menu, " InsertNameCreate " name
will refer to only one sheet.