Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Moving Range Names/Formulas over to new WB

I had this question after viewing Range Name from one WB to another...... Question.

Roy Cox has been helping me troubleshoot a new WB that I'm creating from an old WB.  The Old WB is rather complex.   I have over 100 Range Names that I need to copy/paste from WB-Old to WB-New given their are formulas that are referred to in specific Macros.  How do I copy and paste all the macros without having to recreate each one?  They are all WB Macros (no WS Macros).  

Any help would be appreciated.

B.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I found this on the web

Sub CopyNames()
    Dim Source As Workbook
    Dim Target As Workbook
    Dim n As Name

    Set Source = ActiveWorkbook
    Set Target = Workbooks("Book2.xlsx")

    For Each n In Source.Names
        Target.Names.Add Name:=n.Name, RefersTo:=n.Value
    Next
End Sub

Open in new window

I think the code that Martin found will only work if the new workbook contains identically named sheets.

You might find Jan Karel Pieterse Name Manager Tool useful. I think it may have a button to do what you want. I think it has a button for importing Named Ranges.

List all the names into a new worksheet in your existing workbook - you can do this from the Formulas Tab as I explained in your last question. Edit them if you want - you might want to change the sheet names.

This code will import the names into the active  workbook which should be the new workbook.

I have tested it using a few names and it works for me.
Option Explicit

Sub ImportNames()
    Dim wbTo As Workbook
    Dim oWs As Worksheet
    Dim rNames As Range, rCl As Range

    ''/// assumes this is in the original workbook, add a new sheet & list the names
    ''/// change the name of the sheet to use the name of your sheet
    Set oWs = ThisWorkbook.Sheets("Sheet1")
    ''/// assumes names list is begins in A1
    ''/// define the range containing the list
    Set rNames = oWs.Range("A1").CurrentRegion
    ''/// this is the new workbook
    Set wbTo = ActiveWorkbook    ''/// make sure that the new workbook is active
    ''/// loops through the list and adds the names to the new workbook
    For Each rCl In rNames.Columns(1).Cells
        wbTo.Names.Add Name:=rCl.Value, RefersTo:=rCl.Offset(, 1).Value
    Next rCl
End Sub

Open in new window

Avatar of Bright01

ASKER

Roy and Martin,

Thank you guys for the help here.  The new WB doesn't have the same named sheets yet.  I could copy some over and then try it for those that I copy over.....

Roy....the code you gave me;  Do I put the Macro in the original WB or the Target WB?  I'm not sure what it is asking me to do in terms of

''/// change the name of the sheet to use the name of your sheet

Thanks guys,

B.
OK... I put a Sheet1 in both WBs and ran the macro.  But nothing appeared, nor is there any new Names in the new WB.

What am I doing wrong?

B.
If you have placed my code in a code module in the Source workbook, and you also have the Target workbook open, the code should work. I tested it and it does.
Martin,

I get an error on this line:

    Set Target = Workbooks("Dupont ModelV3.xlsm")

I put the Target Workbook name in here.  But I got the same error.

B.
What error? "Subscript out of bounds"? Is Dupont ModelV3.xlsm spelled correctly and open?
God you're good.  Yes....there was a space missing in the name.  However, now I get another error on this line:

"The Formula You Typed has an Error......"

        Target.Names.Add Name:=n.Name, RefersTo:=n.Value
God you're good.
I've had a lot of experience making the same mistakes:)

What is n.Value when you get the error? In other words what is the formula with which it's having a problem?
Where will it tell me which formula it's stopping at?  It's simply showing an error in the code (Yellow highlighting).  I have the other WB open and also a sheet called Sheet1 added.

B.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The code goes in a standard module in the original workbook. Create the list in a new sheet, amend if required then select the new workbook. Run the macro and the names should be created in the new workbook.
Aaaahhhhh...... got it.  It's stopping at a bad Range Name (ie. #REF.).  Let me clean up all the bad Names and rerun.

B.
Roy and Martin,

I had something interesting happen.  As I explained things to you, you both give me great ideas on how to move over range names.  However, I went back to the originator of the Original WB and he had actually defined all of the Variables (Range Names) in a Module in the Original WB.  I dropped and dragged the module into the new WB and all of them showed up in the Range Names list in the new WB!  My last question to both of you is is this simply great program design (by the original designer) or is there a simple way to put all Range Names in a module for Range Management?

B.
Please attach the module so we can take a look at it, or at least enough of the code so we can get an idea of how it operates.
Martin,

See "GlobalVar"  Module.
RangeName-Module.xlsm
I don't have an opinion about that module.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys.  I'm learning more and more every time I work with you guys.  Big help in beginning this project.  Thanks again.

Best regards,

B.
Pleased to help some one who is keen to learn.
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015