Duplicate name range error when copying sheet (Excel 2010)

[JGeniti]

I have a procedure that copies an array of worksheets from my main
workbook into a blank workbook. What I noticed when I started testing
this in Excel 2010 (from 2003), one of the pages is trying to copy
over range names and I'm getting the duplicate range name error
message. It was my understanding that by default, the range names
would not be brought in by default. This is true for the other 10
sheets that are being copied. Does anyone have any ideas or ways to
force that no range name be brought over?

Thank you

 

[GS]

I have a procedure that copies an array of worksheets from my main
workbook into a blank workbook. What I noticed when I started testing
this in Excel 2010 (from 2003), one of the pages is trying to copy
over range names and I'm getting the duplicate range name error
message. It was my understanding that by default, the range names
would not be brought in by default. This is true for the other 10
sheets that are being copied. Does anyone have any ideas or ways to
force that no range name be brought over?

Thank you

If the names are global (workbook level) they will be tagging along
with all sheets that use them. Once a using sheet is copied, all
subsequent sheets will raise the name conflict message. If the names
are local (sheet level) then there should be no problems. This is
precisely why names should always be local scope unless global scope is
absolutely necessary.

Optionally, you can disable the 'alert' by wrapping your copy sheet
code with...

Application.EnableAlerts = False
'copy code goes here
Application.EnableAlerts = True

 

[JGeniti]

Thank you for the response. I wasn't aware that range names could be
associated globaly or locally, which might explain why only one sheet
out of the 10 is having the issues. I don't know how I set the range
names global, but is there a way to make them local prior to running
my copy?

Thanks

 

[GS]

JGeniti submitted this idea :

Thank you for the response. I wasn't aware that range names could be
associated globaly or locally, which might explain why only one sheet
out of the 10 is having the issues. I don't know how I set the range
names global, but is there a way to make them local prior to running
my copy?

Thanks

Well, the only way to set a defined name 'global' is to deliberately
NOT create it as 'local' by omitting the sheetname and required naming
syntax!

To create local names, (either via the namebox left of Formula Bar OR
via Define names dialog), you MUST wrap the sheetname in apostrophes
and separate the sheetname from the defined name with an exclamation
character.

Example:
'Sheet Name'!RangeName

Thus, if you have multiple sheets with the same structure/layout then
you can assign similar cells with the same name.

Example using a template to create:
Sheets("Jan")
Sheets("Feb")
Sheets("Mar")

You have 2 cells on each sheet that contain the fiscal year and the
fiscal quarter for each sheet.

Activate Sheets("Template")
Name fiscal year cell as follows:
'Jan'!FiscalYear
Name fiscal quarter as follows:
'Jan'!FiscalQtr

Make 3 copies of the template and rename them "Jan", "Feb", and "Mar".
Copy the 3 month sheets to a new workbook and see how there's no name
conflict message.

HTH

 

[GS]

GS presented the following explanation :

Activate Sheets("Template")
Name fiscal year cell as follows:
'Jan'!FiscalYear
Name fiscal quarter as follows:
'Jan'!FiscalQtr

...should read
Activate Sheets("Template")
Name fiscal year cell as follows:
'Template'!FiscalYear
Name fiscal quarter as follows:
'Template'!FiscalQtr

Excel will handle the sheet renaming and associated names automatically
when you rename each copy.