(This is an Excel-specific tech note)
Delete method of Name object fails, even though prior use of the named range succeeds.
The snippet to return a row number succeeds:
myRowNumber = CurrSheet.Range("LASTROW").Row
The snippet to delete the name “LASTROW” from the names collection fails (generating Runtime Error 1004):
I only discovered this in Excel 2000, so I don’t know if anything’s changed in newer versions. I hope to get time to find out soon.
I have a project that involves creating a new workbook from a template, and then each successive pass through the code causes the second worksheet to be copied to the end. In other words, my template file has one “cover” sheet as Sheet1, and a first “data” sheet as sheet2. Each new sheet added to the workbook is obtained by copying Sheet2 to the end, and replacing the data in it with data specific to the new sheet.
To accomplish this in code, the template file has NAMED RANGES assigned to Sheet2. They are Sheet-Level names only. Thus each succeeding copy from Sheet2 to the end results in a new sheet with its own Sheet-Level names, each of which is the same as the names in all other sheets (except Sheet1, the “cover” sheet).
After much trial and error, I discovered in the debugger that the Names collection for the current sheet (Sheet2) had only the Print Area in it, which makes no sense, given that I could dereference the Row property of the named range LASTROW just fine. Looking at the names collection for the Workbook, however showed that the names exist as expected.
Adding to the perplexing nature of this strange behavior is the fact that it has never failed before.
The clue that solved it was that the only time it ever failed was also the only time it was ever executed against the original Sheet2, from which all successive sheets were copied.
In other words, the copied sheets never exhibited this behavior of the Names collection.
The Bottom Line:
I believe each Workbook has only one Names collection. This explains why the Name Manager in newer versions of Excel lets you select whether a named range is Sheet-level or Workbook-level. (It also explains why some descriptions of Sheet-level names claim that an exclamation point is used internally to the Names collection to denote sheet-level names. I have not verified this with Excel 2000, but it makes sense.)
When you attempt to access the names collection for a given Worksheet, AND when that Worksheet has been created from a template file, the Named Ranges are manageable only as Workbook-Level names. If you copy the Worksheet with those names to the end of the sheets in the current Workbook, the name objects will be manageable as sheet-level objects.
The Workaround (for my case)
If CurrSheet.Index = 2 Then
I’m sure a more robust method exists for dealing with names directly, possibly involving the Item, but I haven’t had time to explore it further.
I never tried “activating” the sheet where the runtime error was thrown. But reading the following item at StackOverflow makes it seem like that might be an alternate way of solving this. I hope to get time to make the experiment. Until then, I’ll just add the link and you can see what I mean:
StackOverflow Questions: How do I avoid run-time error when a worksheet is protected in MS-Excel?