I have a workbook that has a sheet (Report_Template) that has several "worksheet scoped" named ranges. Some are dynamic range names, others are not. I then have vba that uses sheets("Report_Template").copy to make a new page that will be customized by additional vba code into a report. What "should" happen is that the worksheet scoped named ranges that exist in Report_Template should be copied into the new worksheet under the same name and should have worksheet scope and that should be the end of it. Excel does always create the worksheet scoped named ranges in the newly minted worksheet and they function as expected. However, "sometimes", that isn't all that happens. "Sometimes", excel also creates an additional copy of those named ranges (same name) but with WORKBOOK scope. When this happens, the workbook scoped copy of the named range has an ill-defined definition and all the cell references in the definition change to #Ref. I don't understand this behavior because a) it only happens sometimes - not sure what triggers it and b) sheets(<sht name>).copy should never create 2 copies of the named ranges (1 local, 1 global) that are local to the sheet being copied. In deciding that excel "sometimes" has this curious behavior, I took care to delete the ill-defined workbook scope names, save the workbook, close excel, reboot, open excel, open my workbook to get a "clean start" before I run my program. When this odd behavior occurs, an alert appears on-screen just after the call to sheets(...).copy that indicates there are 2 named ranges with the same name and asks which one I want to use - for each duplicate named range. The alert is to be expected on occasions where you intentionally create 2 named ranges with one having worksheet scope and the other having workbook scope. However, "sometimes", this behavior happens the first time I run my program after a clean start and sometimes it happens on the 2nd or 3rd time I run my program.
Question: Does anyone see what I might be doing wrong that causes this unexpected behavior? Or, am I missing something about why this behavior is appropriate?
In my case, the "problem" the aforementioned anomolous behavior creates for me is the nuisance of the alerts that appear on-screen when the user fires the program that creates a new report from a template. Users' don't know what to do with the alerts so they get confused and call me for help. A workaround for that problem is to use Application.DisplayAlerts = false before the call to sheets(...).copy but I'd rather find a way to make it so that isn't necessary.
Question: Does anyone see what I might be doing wrong that causes this unexpected behavior? Or, am I missing something about why this behavior is appropriate?
In my case, the "problem" the aforementioned anomolous behavior creates for me is the nuisance of the alerts that appear on-screen when the user fires the program that creates a new report from a template. Users' don't know what to do with the alerts so they get confused and call me for help. A workaround for that problem is to use Application.DisplayAlerts = false before the call to sheets(...).copy but I'd rather find a way to make it so that isn't necessary.