Range Names Disappear

Just to be on the safe side, you could add code which runs after the sheet copying, checking whether all range names you need in the target workbook are really there and if not, create them. Additionally, I'd double check the cells in the source sheets for entries with more than 255 characters, as these get truncated by a sheet copy.

I have been playing with this most of today trying to figure this out and this is what I have found out:

(Looking at Name Manager)

1. Some, but not all names have a “Refers To” that point to the Source workbook instead of the Target workbook. What is also strange is my source file is on a network drive “P:”, but the “Refers To” says “C:” and the rest of the path is correct. At this point the Value is reporting correctly albeit from the Source sheet I presume.

2. On completion of the code posted above I close the Target file and attach it to an email. When you open the attachment from the email all the Named Ranges where they referred to the Source file now have a Value of “#REF!”. So the phantom disappearing Named Ranges that I have found many people talking about may be a broken link in the named range. i.e. if you look at the cell where you expected a named range its gone, but if you look in Name Manager its there, but with a broken reference to the cell.

3. Being new to Name Manager, I then realised that what I was looking at when I selected Name Manager from the Target, was all the Named Ranges for all open workbooks. So with this in mind, what I was seeing is the Source Named Range and a completely missing Named Range from the Target i.e. the VBA copy does not seem to have copied the Named Range.

4. I re-ran the above code and paused it at the point it had copied the second sheet which was the first sheet with a bunch of Named Ranges on it and sure enough it looks like the Ranges did not copy.

5. I decided to manually deleted the sheet and manually copy the sheet back with the code still in a paused state. This gave me some very interesting and varied results:

5.  
   1. Result 1 – Named Range copied ok, range showing Value = “#REF!” and Refers To = “=#REF!xxxx” where xxxx = the correct cell reference.
   2. Result 2 – Named Range did not copy, range to source showing the correct Value,

6. Decided to delete the sheet again and see what is left. The Result 2 above remained unchanged. The good copy from Result 1 has good as you would expect, but the #REF! lines remain.

7. Copy the sheet back and deleted all the #REF! lines and I now seem to be where I was at 4 above. So now I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

8. Decided to close the Target, manually open a new workbook and manually copy the sheet, it worked fine. At this point there is no reference to the Source file for this sheet.

9. Deleted the workbook I manually created, restarted the code and skip over the file create part and manually created a new one. Allowed the code to SaveAs to the temp file name. Then manually copy the sheet, checked and only reference was to the local sheet so all GOOD at this point. Deleted the sheet and allowed the code to continue to copy the sheet as normal (it now BROKEN):

9.  
   1. Result 1 – Named Range did not copy, range showing Value = “#REF!” and Refers To = “=#REF!xxxx” where xxxx = the correct cell reference.
   2. Result 2 – Named Range did not copy, range to source showing the correct Value,

10. Deleted the sheet, deleted all references to ranges with errors and allowed the code to copy again. So now I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

11. Deleted the sheet, deleted all references to ranges with errors and manually copied again, its still BROKEN. No change to the above, i.e. I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

This has to be an Excel bug, it just does not make sense. It seems once the workbook is “broken” it is not possible to manually or via code to successfully copy a sheet.

Plan B

I wrote some code to create me a workbook in 2003, with 30 sheets each with 1000 unique named ranges on them. Copied the file to another computer I have running 2007. Then I slightly modified the code I posted earlier so it would run with my test workbook and it all worked perfectly. (How to kill a couple of hours and not be able to create a simple test program.)

I am running out of ideas here.

Jan,

Thanks for your reply.

I am creating a workbook from a source workbook that has about 20 sheets each with 30+ Named Ranges, so checking every reference and recreating it every time is not exactly practical.

None of the cells have more than 255 characters in them, in fact most of the cells nothing in them (I am creating a blank form for users to enter data).

Trefor

To your points:

1: Very odd, proves something must be wrong here.

2:The refs are there because the source is then missing, so make sense.

3: Name Manager should only show names from active workbook. Are the names alternating global/local to a sheet?

I expect you can overcome your problems by copying all sheets in one statement rather than looping through them.

ActiveWorkbook.Sheets.Copy

copies all sheets to a new workbook.

Jan,

1. Agreed.
2. Yep, makes sense.
3. If I look at the Source workbook I generally only have 1 ref and it points to the correct place, although the Scope = Workbook. Using edit the scope is greyed out, can you only set the scope on creation?

Re your suggestion on copying multiple sheets I have two problems:

1. I need to be able to choose which sheets get copied (I create 6 or 7 files and they each have different combinations. So what I did was pass variables to a sub routine:

Sheets(Array(ExportSheetName1, ExportSheetName2, ExportSheetName3, ExportSheetName4, ExportSheetName5, ExportSheetName6, ExportSheetName7, ExportSheetName8, ExportSheetName9, ExportSheetName10, ExportSheetName11, ExportSheetName12, ExportSheetName13, ExportSheetName14, ExportSheetName15, ExportSheetName16)).Select

 Sheets(Array(ExportSheetName1, ExportSheetName2, ExportSheetName3, ExportSheetName4, ExportSheetName5, ExportSheetName6, ExportSheetName7, ExportSheetName8, ExportSheetName9, ExportSheetName10, ExportSheetName11, ExportSheetName12, ExportSheetName13, ExportSheetName14, ExportSheetName15, ExportSheetName16)).Copy

2. The other issue is I have one file that needs about 25 sheets and the Array seems to have a limit on how many sheets it can copy.

Strange thing is this has been working in 2003 for years.

I have also posted on another forum:

http://smurfonspreadsheets.wordpress.com/2009/02/24/excel-2007-lost-names/#comment-15117

Patrick suggested I round trip my workbook through HTML and while I am new to this concept and it cost me a lot of work today, it did highlight that Excel 2007 was not happy with a number of Named Ranged and the good thing was the ones 2007 kept (about 60%) had the correct Scope for the sheet. Problem is round tripping completely guts my workbook to the point I would have to start again. So the question is how can I find and fix all the suspect Named Ranges without round tripping?

Is it possible for example to write some VBA that will export all the named ranges to a file and I can reapply to my non round tripped file?

First of all, roundtripping through html in Excel 2007 is less useful than it used to be with 2003, because you loose a lot more fidelity.
Secondly, to copy a set of sheets, use code that selects the sheets to be copied and then use Selectedsheets.copy.
To add sheets to a group:

'Select first sheet:
ActiveWorkbook.Sheets("Sheet1").Select True
'Add next sheet:
ActiveWorkbook.Sheets("Sheet3").Select False

If you copy one sheet in which a range name is used which points to another sheet, then the resulting workbook will inherit a range name, which points to the ORIGINAL file. Copying the sheets as a group will ensure the respective range names will keep pointing to the NEW sheets rather than to the old sheets. That is why you need to copy all sheets in one go.

I suggest you download my Name Manager and use that to troubleshoot. It can also write a list of your range names to a worksheet so you can bulk-edit them. Afterwards, you can use it to build range names from that list in one go.

www.jkp-ads.com/officemarketplacenm-en.asp

Jan,

Sorry for the delay in getting back to you.

Before I had even read your reply to my last post I had already found and downloaded your Name Manager. WOW!!! This certainly has made this exercise much easier. I didn’t even realise just how many named ranges I had in my workbook (over 2,500). Anyway I have spent the last couple of days cleaning everything up and I feel much happier with the state of my project. So a big thankyou for Name Manager.

Your suggestion re grouping makes perfect sense and seems so obvious now and even better it fixed my problem as well. So again thank you.

Trefor

You're welcome.

Glad to hear you could solve the problem!