Excel 2007 - Using a Named Ranges in lieu of Cell References
 

Can anyone tell me how I can use a Named Range (in my case "OSRD") in
lieu of the actual cell reference as shown below. The formual below
copies the Range reference to every worksheet within my workbook.
However, if i add any rows above this cell reference, the cell Named
Range "OSRD" is automatically updated with the new range on the Active
Worksheet, however, because the formula below references a specific
range and then asssigns a name to it, (in this case OSRD) it will not
update the Range Name automatically when it copies it to all other
worksheets.

My problem is that i have created a Macro that copies one specific
worksheet and then saves it to a new sheet but the original cell
reference (A114:A141) is assigned the Range Name OSRD and does not
represent the new updated Range with the inserted rows.

One more point is that the rows being inserted are not related to the
Range Name. I am creating a name range OSRD which needs to be deleted
from the newly created worksheet only, and left in tack on the
original worksheet.

I hope that makes sense.

Any help is appreciated
Rich

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Range("A114:A141").Name = " '" & ws.Name & "'!OSRD"


Next ws