[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4364
  • Last Modified:

Copying Defined Names in Excel

EE Pros,

I have a large number of "Range Names" that I'm trying to copy from one Worksheet to another.  There doesn't seem to be an easy way to do this; forcing me to redo each of the Range names.  Is there a way to copy a number of Range names at one time from one Workbook to another.

Thank you in advance.

B.
0
Bright01
Asked:
Bright01
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
MichaelBusiness AnalystCommented:
Hi B.

you can try this solution. Be sure to have both workbooks open, have the source workbook active and change the name of your destination workbook in the macro accordingly:

 Sub Copy_All_Defined_Names()
   ' Loop through all of the defined names in the active workbook.
     For Each x In ActiveWorkbook.Names
      ' Add each defined name from the active workbook to the target workbook ("Book2.xls").
      ' "x.value" refers to the cell references the defined name points to.
      Workbooks("Book2.xls").Names.Add Name:=x.Name, _
         RefersTo:=x.Value
   Next x
End Sub

Open in new window

Be sure to test this code first on a copy of your workbooks.

Joop
0
 
Ejgil HedegaardCommented:
If you copy the sheet to the other workbook, all names with reference to that sheet, will also be copied to the new workbook, and have the same sheet references.
0
 
Bright01Author Commented:
Is there any way to copy (besides one at a time) without using a Macro?  And if I do use the Macro, do I put it in a Module in the target WB or the specific target WS?

Thank you,

B.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MichaelBusiness AnalystCommented:
You can try hgholt's suggestion for a non-macro solution.

If you use the macro, place it in either workbook in a module. As long as you have the source workbook active when you execute it.

Joop
0
 
Ejgil HedegaardCommented:
You can copy all sheets in one process.
Activate the first sheet.
To group a series of sheets hold down the Shift key and point at the last sheet tab.
To group non contiguous sheet, hold down the Ctrl key and point at the sheet tabs one at a time.
Then right-click at one of the activated tabs and copy the sheets.
To deactivate the sheet group activate a  sheet not in the group.
0
 
Bright01Author Commented:
Hgholt,

Just to be clear, I'm trying to copy "names" (Formulas; Range Names) over.  I understand that if I copy a sheet or multiple sheets the range names come over, but if I'm simply coming Range Names in the  Name Manager, I can not seem to copy multiple names at one time.  That means if I don't copy the sheets but want to copy the Names, I have to recreate the Names one at a time.  That's what I'm trying to see if there is a way to simply copy and paste or drop and drag.  

Does your approach by coping over the sheets and then not activating them.....will that work?

B.
0
 
yuppyduCommented:
There is a very useful add-in developed by Rob Bovey which adds a set of utility in the excel ribbon. One of them lets you export all range names in a separate sheet. From there you can easily recreate all range names in a new workbook.
The link to get the add-in for free: www.appspro.com/Utilities/ExcelUtilities.htm
0
 
Bright01Author Commented:
Thanks guys; I can use all of these methods but I really like the utility!

All the best,

B.
0
 
yuppyduCommented:
Well thanks Rob!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now