How to modigy the reference in a named range?

[rick]

I have a worksheet for Financial Reporting called FR2(n) - where n can be
from 1 to 15. A new sheet is generated at the end of the previous reporting
period. I need to pick up 8 'total' rows from the latest sheet and copy them
into an analysis sheet. So I created sheet FR2(1) and made 8 named ranges
for the totals.

Of course the Names Manager shows that the range only refers to the FR2(1)
sheet. When I create the new FR2(2) sheet the named ranges are there but
they refer to RP2(1).

How can I modify the named range sheet references to be the new RP2(2) (etc)
target.

Thanks.

Rick

 

[joel]

The names ranges are a workbook object and you can't tell which name
range is on which sheet. You can change the name range using th
replace method like this

For Each nm In ThisWorkbook.Names
nm.RefersTo = Replace(nm.RefersTo, "Sheet1", "Sheet2")
Next n

 

[rick]

Thank you Joel.
.... Rick