Worksheet Specific

Worksheet Names Collection

Local named ranges belong to the Names collection associated with a particular worksheet.
Worksheet level named ranges are worksheet specific and are normally only used on the worksheet where they have been defined.
To access named ranges in a particular worksheet you can use:

Application.ActiveSheet.Names() 

Since Application is the default member you could abbreviate it and use:

ActiveSheet.Names() 

It is also possible to refer to the named ranges in a particular worksheet.

ActiveWorkbook.Worksheets("Sheet1").Names() 

If you create worksheet level named ranges it is possible to use the same name on more than one worksheet in the same workbook although this is not recommended.

Creating Worksheet Specific Names

You can create worksheet specific named ranges in a number of different ways.
From a Worksheet Object
All worksheet specific named ranges can only be used when a particular worksheet is active

Worksheets("Sheet1").Names.Add Name:="MyNamedRange2", _ 
                               RefersTo:=Range("$A$1:$B$8") 

It is also possible to create a worksheet specific named range for the active worksheet.

ActiveSheet.Names.Add Name:="MyNamedRange2", _ 
                      RefersTo:=Range("$A$1:$B$8") 

From a Workbook Object
You can also create a worksheet specific named range using the Workbook object.
You must prefix the name of the named range with the name of the specific worksheet.
In this case we are creating a worksheet specific named range for the worksheet called "Sheet1".

ActiveWorkbook.Names.Add Name:="Sheet1!MyNamedRange2", _ 
                         RefersTo:=Range("Sheet1!$A$1:$B$8") 

When your worksheet names contain spaces then you must surround the worksheet name with single apostrophes.

ActiveWorkbook.Names.Add Name:="'Sheet 2'!MyNamedRange1", _ 
                         RefersTo:=Range("'Sheet 2'!$A$1:$B$8") 

Equal Sign Abbreviation

An alternative to using the Range method is to just use an Equal Sign

Relative References

When you create a named range you should always make the cell references absolute by using the dollar sign ($).
If a named range is created without an absolute cell reference the address will refer to a cell address relative to the cell that was active when the name was created.
Creating the following named range WILL NOT create a named range that refers to cell "A2".
If you select cell "B2" and run the following line of code you will actually get the named range "=Sheet1!B3".

Worksheets("Sheet1").Names.Add Name:="MyNamedRange2", _ 
                               RefersTo:=Range("A2") 

The very odd thing is that when you select the named range it re-defines itself to refer to the cell below the active cell.

Using the Range Object

Range("A1:B8").Name ="Sheet1!MyRangeName" 
Cells(1,1).Resize(8,2).Name = "range_name" 

Using Worksheet Specific Names

Range("Sheet1!namedrange").Value = 30 
Range("my_name").Value = "text" 

[my_name].Value = "text" 

Important

When you use a reference such as Activesheet.Name or Worksheets("Sheet2").Names you are referring to the Names collection of that particular worksheet.
The Add method is the only way to create named ranges that refer to numbers, strings or formulas.