How to modigy the reference in a named range?
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 |
How to modigy the reference in a named range?
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 |
How to modigy the reference in a named range?
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 |
All times are GMT +1. The time now is 09:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com