Bright01
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.
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.
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.
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
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.
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.
ASKER
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.
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.
ASKER
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.
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?
ASKER
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
"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?
ASKER
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.
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Aaaahhhhh...... got it. It's stopping at a bad Range Name (ie. #REF.). Let me clean up all the bad Names and rerun.
B.
B.
ASKER
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.
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.
ASKER
I don't have an opinion about that module.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
Open in new window