copy named ranges from one worksheet to the next worksheet in excel

I am lost with named ranges.
I am setting up formulas on a template sheet with named ranges etc. I want to copy the sheet cells with the formulas on the other 17 sheets I have but the named ranges remain linked to that first sheet. I cannot find how to ensure that the named range becomes localised everytime I work on a sheet, and only refers to that sheet. I have searched the database here, and looked up google, and even downloaded Name Manager, but when I ask it to globalise the name, nothings happens. I am really totally baffled by ho to get about for a problem that seemed to me to be trivial at start!
I am sure it is simple, but I do not even start to see how to go about! I changed the entry of the name and removed the sheet and "!", but when I close, it reverts back to the old name! When I use name manager, and want to globalise, it tells me it is globalised, so???
I want the same cells in everysheet with the same name but referring to the cells in each sheet when that sheet is active.
Thank you!
Daniele QuestiauxResearch associateAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
No problem. You can define scope in 2003 and earlier, but it is not as apparent.

If you want to name a cell with a sheet scope, then you have to enter the name in the name box preceded by the sheet name. See the screenshot. I'm in the process of defining the range name "redcell" with a scope of Sheet1. The name "greencell" has already been defined and the scope shows in the list of range names.

 range name scope in 2003
Again, you can not change the scope after the name has been defined. If you get it wrong, delete the name and start over.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

when you define the ranges in the first sheet, you need to set the scope to the sheet instead of the workbook. You cannot change the scope of a range name after it has been defined.

 scope  scope cannot be changed
With all the range names referring to the sheet, you can then copy the sheet and the names in the new sheet will refer to that sheet.

See attached. Copy sheet1, which has 2 names and see that there will now be four names, two referring to Sheet1 and the other two referring to the copy of Sheet1.

cheers, teylyn

NameScope.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Mind you that you cannot simply copy the cells to a new sheet. You need to copy the whole sheet in order to carry over the range names that refer to that new sheet.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Daniele QuestiauxResearch associateAuthor Commented:
Hi!

I did not mention that I am still using excel 2003 where there is no entry for the scope. I suspect it is a very good addition to excel 2007 and 2010?
Sorry not to have been clear at the start!
Danièle
0
 
Daniele QuestiauxResearch associateAuthor Commented:
Thanks!
I sent a comment a few hours ago to thanks you for the really thorugh answer but it does not seem to have gone through? Sorry about that because your reply was so quick, and I tried it straight away. What was the essential problem turned out to be the fact that I was copying PART of the sheet containing the named range, but not the WHOLE sheet as you mentioned in your short extra comment.Thank you for pointing out that single important and essential point.
As expected, all is now working wonderfully well.!
Danièle
0
 
Daniele QuestiauxResearch associateAuthor Commented:
The extra comment about copying the sheets as a WHOLE rather than only part of it was the crucial point, and made the solution "foolproof".
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.