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

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147605

Thank you Joel.
.... Rick


"joel" wrote in message
...

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=147605