I'm developing a workbook that makes extensive use of names, both for ranges and formulas. These names are defined with scope local to each worksheet, and there are many worksheets. When I make a copy of a worksheet of course it copies the names from the source sheet, with the result that In total there are almost 9000 names defined. Now it takes forever to save the workbook, or to make a copy of a worksheet. Has anyone else run into this type of problem, and what did you do? Thanks.
Hi Agnoth,
This is quite unusual that the workbook is having around 9000 names defined it it. Are you sure are the names are working fine or some are invalid, if yes please remove the invalid ones.
Now the problem is to save the workbook but which one - the old one from where the sheet get copied out or the new one where it has come?
Please explain. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
I understand the need/desire to use named ranges to make formulas easier to read, but I cannot imagine any need for that many named ranges. Maintaining them would be more of a nightmare than just using regular references.
To me, this just screams of a need for data reorganization and a hard look at the formulas you are using. If you post a sample workbook and describe the purpose and intent, you might get some help that gets to the root of the problem, rather than just telling you the limit of Named Ranges (which has to do with system memory, if what I read on Google is correct).
Last edited by Whizbang; 01-06-2012 at 04:23 PM.
You might benefit from cleaning up any dead names. Here's some code from OZgrid that I use to cut out the deadwood:Sub DeleteDeadNames() Dim nName As Name For Each nName In Names If InStr(1, nName.RefersTo, "#REF!") > 0 Then nName.Delete End If Next nName End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks